If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query Puzzle
Hi
I have 4 separate tables of which all have fields "Surname", "Forename" and "DOB", plus the data column. Tbl_0 = S | F | DOB (this is all 5 users) Tbl_1 = S | F | DOB | Data1 Tbl_2 = S | F | DOB | Data2 Tbl_3 = S | F | DOB | Data3 Index above = S (Surname)| F (Forename) | DOB | Data I can build individual queries like; Tbl_0 & 1 which lists all the Users that have a Data1 enter - Totaling 1 Tbl_0 & 2 which lists all the Users that have a Data2 enter - Totaling 2 Tbl_0 & 3 which lists all the Users that have a Data3 enter - Totaling 2 My problem is that I want to see them all in one table. For Example; Users | Data1 | Data2 | Data3 ----------------------------------- Joe text - - Bill - text text John - - text Bob - text - Peter - - - Peter should not even show in the list above, because he doesn't have any data to display. If this is possible how should I build the Query? Regards Bradley |
#2
|
|||
|
|||
Something sounds wrong here.
Are all of the tables capturing essentially the same information? It sounds like you are using the table itself as data. The fact that the information is about person X should be captured in rows in a table, not as a separate table. If the DataN fields are how you are differentiating your records, then you should probably have a column that indicates the type of record this is and put all of them in a single table. This doesn't answer your question, but may save you much grief down the road (and not too far down that road!) Good Luck! -- Chaim "Bradley" wrote in message ... Hi I have 4 separate tables of which all have fields "Surname", "Forename" and "DOB", plus the data column. Tbl_0 = S | F | DOB (this is all 5 users) Tbl_1 = S | F | DOB | Data1 Tbl_2 = S | F | DOB | Data2 Tbl_3 = S | F | DOB | Data3 Index above = S (Surname)| F (Forename) | DOB | Data I can build individual queries like; Tbl_0 & 1 which lists all the Users that have a Data1 enter - Totaling 1 Tbl_0 & 2 which lists all the Users that have a Data2 enter - Totaling 2 Tbl_0 & 3 which lists all the Users that have a Data3 enter - Totaling 2 My problem is that I want to see them all in one table. For Example; Users | Data1 | Data2 | Data3 ----------------------------------- Joe text - - Bill - text text John - - text Bob - text - Peter - - - Peter should not even show in the list above, because he doesn't have any data to display. If this is possible how should I build the Query? Regards Bradley |
#3
|
|||
|
|||
Bradley wrote:
I have 4 separate tables of which all have fields "Surname", "Forename" and "DOB", plus the data column. Tbl_0 = S | F | DOB (this is all 5 users) Tbl_1 = S | F | DOB | Data1 Tbl_2 = S | F | DOB | Data2 Tbl_3 = S | F | DOB | Data3 Index above = S (Surname)| F (Forename) | DOB | Data I can build individual queries like; Tbl_0 & 1 which lists all the Users that have a Data1 enter - Totaling 1 Tbl_0 & 2 which lists all the Users that have a Data2 enter - Totaling 2 Tbl_0 & 3 which lists all the Users that have a Data3 enter - Totaling 2 My problem is that I want to see them all in one table. For Example; Users | Data1 | Data2 | Data3 ----------------------------------- Joe text - - Bill - text text John - - text Bob - text - Peter - - - Peter should not even show in the list above, because he doesn't have any data to display. Looks like an unnormalized mess to me. This should be don with two tables, one for the names and one additional table for the data. However, in this specific case (not in general), I think you can work around the difficulties of the relational database rules violations with something like: SELECT F, Data1, Data2, Data3 FROM ((tbl0 LEFT JOIN tbl1 ON tbl0.S = tbl1.S and tbl0.F = tbl1.F) LEFT JOIN tbl2 ON tbl0.S = tbl2.S and tbl0.F = tbl2.F) LEFT JOIN tbl3 ON tbl0.S = tbl3.S and tbl0.F = tbl3.F WHERE Data1 Is Not Null OR Data2 Is Not Null OR Data3 Is Not Null -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Too Few Parameters error Mail Merge Access Parameter Query | Tony_VBACoder | Mailmerge | 3 | September 14th, 2004 12:15 PM |