I am relatively new to Access and have just spent the better part of this day trying to find a solution (if there is one)I have 4 tables with a User ID relationship (information is received from an on line form) If there is no information entered into one of the 4 tables the query does not get the results of that User Id information from the other 3 tables. Example: We have 100 customers fill out a 4 page form. Each page is stored in a separate table. When I execute my query it results in only 46 customers. If a customer blows past any one of the 4 pages the query will not pick up that customer's information. What is the work around for this situation? I've tried changing the way the tables are "joined", tried "is null" criteria, tried separate queries on each table and then a query on these queries all with the same results!!!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Incorrect Querry Results from tables with 'null' values
- Thread starter CI
- Start date
LonnieJohnson
Programmer
Can you have one your form's (the first one) require the user to put something in. Thus one of your tables will have some data for that customer. Then the one-to-many relationship will work.
ljprodev@yahoo.com
Professional Development
MS Access Applications
ljprodev@yahoo.com
Professional Development
MS Access Applications
[red]Correct![/red]
A table for each page...hmmm, bad idea. And for exactly the reason that you have expressed.
There are a couple of ways to rectify this situation.
One is to force the input forms to cross the table boundary ,that is; store the last few items currently being added to Table1 in Table2 instead, along with the CustomerID. Doing the same with each subsequent page.
The second is to store the results in one table. Yea' though rather wide perhaps, you never really have to use that table or the record itself. I'd run a few append queries that extract the data from the newly created record and add it to the three or four tables that you'd like to use in your application. Then just delete the source record! This could be called a 'trade off'. Execution time vs. complexity and size, or simply, pay now or pay later. By the way, welcome to the club.
I guess the other is to continue to live with the 'a page per table' design (or is it the other way around?). Sorry.
Solution might be to create a table with each Customer and CustomerID if one does not already exist.
This is the 'left' side of your join expression.
Next join each table using the the CustomerID in the left hand table. Using a 'left' join. Do not try and 'chain' the tables. You already know that this will not work.
Fire off the query and you should have four rows for each customer that stuck around long enough to complete each of the four form pages. For those customers that did not complete one or more of the pages, you will have fewer than four lines (for each 'left' hand row).
You'll then have to 'glean' your desired result from theses rows using and additional query or queries, or code. This is about he best that can be had under the circumstances.
More? let me know. Amiel
amielzz@netscape.net
A table for each page...hmmm, bad idea. And for exactly the reason that you have expressed.
There are a couple of ways to rectify this situation.
One is to force the input forms to cross the table boundary ,that is; store the last few items currently being added to Table1 in Table2 instead, along with the CustomerID. Doing the same with each subsequent page.
The second is to store the results in one table. Yea' though rather wide perhaps, you never really have to use that table or the record itself. I'd run a few append queries that extract the data from the newly created record and add it to the three or four tables that you'd like to use in your application. Then just delete the source record! This could be called a 'trade off'. Execution time vs. complexity and size, or simply, pay now or pay later. By the way, welcome to the club.
I guess the other is to continue to live with the 'a page per table' design (or is it the other way around?). Sorry.
Solution might be to create a table with each Customer and CustomerID if one does not already exist.
This is the 'left' side of your join expression.
Next join each table using the the CustomerID in the left hand table. Using a 'left' join. Do not try and 'chain' the tables. You already know that this will not work.
Fire off the query and you should have four rows for each customer that stuck around long enough to complete each of the four form pages. For those customers that did not complete one or more of the pages, you will have fewer than four lines (for each 'left' hand row).
You'll then have to 'glean' your desired result from theses rows using and additional query or queries, or code. This is about he best that can be had under the circumstances.
More? let me know. Amiel
amielzz@netscape.net
- Status
Similar threads
- Locked
- Question
- Locked
- Question
