Missing records in Access query
March 26, 2019 8:18 AM Subscribe
I have an Access database with three tables, and a main form that we use for inputting data, based on one of the tables, with the other two tables as subforms.
My issue is that, unless we have input some data on both subforms, the record won't show up in any queries.
I've created a workaround by having a "file open" tickbox on each of the two subforms, in case we're not putting any other information on those when creating a new record, but I'd prefer if my staff didn't have to remember that extra step. Surely there is a way to 'activate' the record and have it show up in queries without having things in all three tables?
I did build this database but I am totally self-taught in Access and therefore probably missing a lot of the basics! I've tried searching for answers to this but it's hard to narrow down the search terms to come up with anything useful.
I've created a workaround by having a "file open" tickbox on each of the two subforms, in case we're not putting any other information on those when creating a new record, but I'd prefer if my staff didn't have to remember that extra step. Surely there is a way to 'activate' the record and have it show up in queries without having things in all three tables?
I did build this database but I am totally self-taught in Access and therefore probably missing a lot of the basics! I've tried searching for answers to this but it's hard to narrow down the search terms to come up with anything useful.
Yes, there is. I'll give you hopefully an understandable explanation of the database theory which should help you research the right fix for how you are using Access.
What you are seeing is the consequence of an "Inner Join" vs. a "Left Join". I'll simplify my examples to only include two tables but the same principle applies to more tables. In an Inner Join, which is the default type of join, you are only going to see data where there is matching data in both tables. Example query:
To show all customer data, you need to either query just customer data OR if you need the order data as well in this one query you need to explore different types of joins. This is where Microsoft Access' terminology/sql syntax deviates a bit from the standard language used by other databases. Safe it say, there are join types that contain all of one table and only the data in the second table that matches. If I recall correctly, in Access' visual query builder, if you double click on the links between tables you will get options on how to join the tables.
posted by mmascolino at 8:41 AM on March 26, 2019
What you are seeing is the consequence of an "Inner Join" vs. a "Left Join". I'll simplify my examples to only include two tables but the same principle applies to more tables. In an Inner Join, which is the default type of join, you are only going to see data where there is matching data in both tables. Example query:
SELECT customers.ID, customers.TheName, orders.orderItemNameThis is only going to show customer and order data for where the particular customer has order data in the orders table.
FROM orders INNER JOIN customres ON orders.nameid = customers.TheId;
To show all customer data, you need to either query just customer data OR if you need the order data as well in this one query you need to explore different types of joins. This is where Microsoft Access' terminology/sql syntax deviates a bit from the standard language used by other databases. Safe it say, there are join types that contain all of one table and only the data in the second table that matches. If I recall correctly, in Access' visual query builder, if you double click on the links between tables you will get options on how to join the tables.
posted by mmascolino at 8:41 AM on March 26, 2019
Definitely agree that it sounds like the query underlying the form has inner joins between the main form's table and the subforms' tables .
If you build the main form bound to just the main table, and then build subforms bound to the subtables, and then add the subforms to the main form, I think the automatic linking thing that Access does for subforms (there should be a dialog with parent/child fields) is supposed to handle this for you.
But if you don't want to redesign that much, and you did bind the main form to a query that has joins to the subtables, then changing the join types in that query should work.
posted by thelonius at 8:46 AM on March 26, 2019
If you build the main form bound to just the main table, and then build subforms bound to the subtables, and then add the subforms to the main form, I think the automatic linking thing that Access does for subforms (there should be a dialog with parent/child fields) is supposed to handle this for you.
But if you don't want to redesign that much, and you did bind the main form to a query that has joins to the subtables, then changing the join types in that query should work.
posted by thelonius at 8:46 AM on March 26, 2019
This is where Microsoft Access' terminology/sql syntax deviates a bit from the standard language used by other databases
It would just be:
SELECT customers.ID, customers.TheName, orders.orderItemName
FROM orders LEFT JOIN customres ON orders.nameid = customers.TheId;
which, as far as I can see, differs from ANSI SQL only in that it does not go "LEFT OUTER JOIN". THe "OUTER" seems redundant to me anyway - there is no such thing as a "LEFT INNER JOIN", is there?
posted by thelonius at 9:19 AM on March 26, 2019
It would just be:
SELECT customers.ID, customers.TheName, orders.orderItemName
FROM orders LEFT JOIN customres ON orders.nameid = customers.TheId;
which, as far as I can see, differs from ANSI SQL only in that it does not go "LEFT OUTER JOIN". THe "OUTER" seems redundant to me anyway - there is no such thing as a "LEFT INNER JOIN", is there?
posted by thelonius at 9:19 AM on March 26, 2019
A little extra about what folks are saying above. If you look at the query in design view, with the boxes and arrows, we think you're seeing the table-boxes connected by a line that terminates in a dot on both ends. This instructs the query to show you a record ONLY IF there is a corresponding record in both tables.
So you can do it th like they're saying above in the query's SQL View, or you can do it in the query's Design View like this: right click on the line between the table-boxes and pick "Join Properties" Then pick the 2nd or 3rd option depending on which table always has a pertinent record in it.
We're all assuming that you've got your joins pointing at the right fields. Good luck, we're all counting on you.
posted by turkeybrain at 1:19 PM on March 27, 2019
So you can do it th like they're saying above in the query's SQL View, or you can do it in the query's Design View like this: right click on the line between the table-boxes and pick "Join Properties" Then pick the 2nd or 3rd option depending on which table always has a pertinent record in it.
We're all assuming that you've got your joins pointing at the right fields. Good luck, we're all counting on you.
posted by turkeybrain at 1:19 PM on March 27, 2019
Response by poster: Thanks for all of these great answers, I'm going to dig around today and try to implement!
posted by sabotagerabbit at 6:56 AM on March 28, 2019
posted by sabotagerabbit at 6:56 AM on March 28, 2019
Response by poster: Thanks again, all. These answers, plus the link in advicepig's response, gave me enough info to figure it out!
posted by sabotagerabbit at 7:32 AM on April 26, 2019
posted by sabotagerabbit at 7:32 AM on April 26, 2019
« Older Is there such a thing as a real estate blacklist | How do I use a Pivot Table to count across... Newer »
This thread is closed to new comments.
This describes it from an Access point of view. https://support.office.com/en-gb/article/join-tables-and-queries-3f5838bd-24a0-4832-9bc1-07061a1478f6
posted by advicepig at 8:31 AM on March 26, 2019 [2 favorites]