Share data from 2 tables

Mark Powell 0 Reputation points
2026-09-13T13:12:45.14+00:00

Sorry new to access so if a basic question please forgive me

i have 2 tables

lead

Activity

They both have a common id number

i want to be able to produce and lead that doesnt have a activity

can anyone help

thanks in anticipation

Microsoft 365 and Office | Access | Development
0 comments

3 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-09-13T13:13:01.5233333+00:00

    Yes. In Access, the usual way to find records in one table that do not have matching records in another table is to create a query with a join on the common ID field, then return only the records where the related table has no match.

    A practical way to set it up:

    1. Make sure both tables can be joined on the same ID field.
      • The related fields must have matching data types.
      • If creating a table relationship, the referenced table should have a primary key or unique index.
    2. In Access, open Create > Query Design.
    3. Add both tables:
      • lead
      • Activity
    4. Create the join by dragging the common ID field from lead to the common ID field in Activity.
      • A line between the tables shows the join.
      • Double-click the join line to open Join Properties.
    5. Choose the join option that includes all records from lead and only matching records from Activity.
      • This is a left outer join.
    6. Add the fields from lead that should appear in the results.
    7. Add the ID field from Activity to the grid, and in its Criteria row enter: Is Null
    8. Run the query.
      • The result will be leads that do not have a matching activity.

    Why this works:

    • Access uses joins in queries to combine data from related tables.
    • With an outer join, all rows from lead are kept.
    • Filtering the Activity side to Is Null returns only leads with no related activity.

    If Access shows relationship errors while setting this up, check these first:

    • Both ID fields use the same data type.
    • The referenced field has a primary key or unique index if a relationship is being created.
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

    1 person found this answer helpful.
  2. Aetherin 1,725 Reputation points Independent Advisor
    2026-09-13T13:36:55.3433333+00:00

    Hi @Mark Powell,

    It's a pleasure to help. Please don't worry, this is a very reasonable question, especially when you're just getting started with Access. I'd be happy to share what I know in case it helps point you in the right direction.

    Since both tables share a common ID field, the easiest way in Microsoft Access is to use the Find Unmatched Query Wizard, which is specifically designed to find records in one table that do not have matching records in another table.

    For your scenario, you can try the following:

    • Go to Create > Query Wizard.

    User's image

    • Select Find Unmatched Query Wizard.

    User's image

    • Choose Lead as the table containing the records you want returned.
    • Choose Activity as the table containing the related records.
    • Select the common ID field to match the tables.
    • Choose any Lead fields you want displayed in the results.
    • Finish the wizard.

    The resulting query will show all Lead records that do not have a corresponding Activity record.

    For more detail, please refer: Compare two tables and find records without matches | Microsoft Support

    I hope this helps. If anything is unclear, or if you run into any difficulties while setting up the query, feel free to reply to me, I'll be happy to share any additional thoughts that might help.

    Best of luck with your Access project!

    If the answer is helpful, please kindly click "Yes" button below. If you have extra questions about this answer, please click "Comment".    

    Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

    Was this answer helpful?

    2 people found this answer helpful.
  3. Duane Hookom 26,935 Reputation points Volunteer Moderator
    2026-09-13T13:42:20.6333333+00:00

    It sounds like the Lead table is a parent to the Activity table. If this is the case you would typically have a form based on the Lead table with a subform based on the Activity table. You would not need Activity records in order to add and update a Lead table record.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.