Hi Natalie,
Your third option is the best one to go with:
Or maybe my third option is to figure out how to create a query that will
query for all the User ID's from the main table even if there is no User ID
match from the inspection schedule.
In query design view, double-click on the join line between the two tables.
You should see a Join Properties dialog box displayed. Click on a button that
reads Join Type. Select either the 2nd or 3rd choices, whichever is
appropriate for your case. You have just created what is known as an outer
join. The default join, which shows results only for matching records in both
tables, is known as an Inner Join.
After clicking on OK twice to dismiss this dialog, you can see the effect by
clicking on View | SQL View, while still in the query design view. If you
look at the SQL (Structured Query Language) statement, you should see "Outer
Join" (it might be a left or right outer join) where there was once the words
"Inner Join" (corresponds to the first join type).
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
Natalie said:
Here is my problem: I have a main form with 1035 records; and a subform table
that only has 733records. I want to create a query that combines the two
tables without loosing the 302 records. I have tried and I cannot think of
another way to do it.
Here are some of the solutions I have thought of:
One option is to have the subform to create a new record automatically
whenever the mainform gets a new record (basically create a space holder).
My second choice is to add the subform table to my main form query, but my
mainform/subform relationship is a one to many relationship and if I change
the date from one record it will also update the second record if it has the
same user ID. Here is my setup:
Main Form:
[User ID]-primary key
[address]
Subfrom
[Inspection ID]-primary key
[User ID]- linked to main form
Or maybe my third option is to figure out how to create a query that will
query for all the User ID's from the main table even if there is no User ID
match from the inspection schedule.