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 with three tables
I posted earlier and was able to pull a query using Unrelated Records but now
I need further assistance. I have three tables: One has 15,000 records of account numbers and names in two different fields One has 8000 records with some of the same account numbers and names plus Cars as a third field 1One has 4000 records with some of the same acount numbers and names plus Trucks as the third field So all Account numbers and names are used for the same three tables. What I need is a report/query withe the Account Numbers and Names of people that have not bought a car and the same for people who have not bought a truck. I tried creating joins,relationshipsand adding Is Null but all I get either no records displaying or the 15,000. Can you please assist me with simplied yet detailed instructions? Thank You |
#2
|
|||
|
|||
Query with three tables
On Fri, 18 May 2007 11:25:01 -0700, joey
wrote: I posted earlier and was able to pull a query using Unrelated Records but now I need further assistance. I have three tables: One has 15,000 records of account numbers and names in two different fields One has 8000 records with some of the same account numbers and names plus Cars as a third field 1One has 4000 records with some of the same acount numbers and names plus Trucks as the third field So all Account numbers and names are used for the same three tables. What I need is a report/query withe the Account Numbers and Names of people that have not bought a car and the same for people who have not bought a truck. I tried creating joins,relationshipsand adding Is Null but all I get either no records displaying or the 15,000. Can you please assist me with simplied yet detailed instructions? Thank You No, because you did not provide enough information: your table names and field names. We cannot see your database! Have you tried the "Unmatched Query Wizard" on the new query screen? That's just what it's designed to do. If that's not working for you please post back with the table and relevant fieldnames. John W. Vinson [MVP] |
#3
|
|||
|
|||
Query with three tables
Here Goes:
Table 1 fields All Account Numbers All Account Names Table 2 fields: Account Numbers Account Names Cars (not yes or no) Table 3 fields Account Numbers Account Names Trucks (not yes or no) Unmatched Query Wizard results show me the account numbers, names and Cars or Trucks (like a "Yes" Result) I am looking for "orphan" Account Numbers and Account Names that are not associated with Cars/Trucks. Could the problem be that only Table 1 has all the Account Numbers/Account Names? I am stuck! "John W. Vinson" wrote: On Fri, 18 May 2007 11:25:01 -0700, joey wrote: I posted earlier and was able to pull a query using Unrelated Records but now I need further assistance. I have three tables: One has 15,000 records of account numbers and names in two different fields One has 8000 records with some of the same account numbers and names plus Cars as a third field 1One has 4000 records with some of the same acount numbers and names plus Trucks as the third field So all Account numbers and names are used for the same three tables. What I need is a report/query withe the Account Numbers and Names of people that have not bought a car and the same for people who have not bought a truck. I tried creating joins,relationshipsand adding Is Null but all I get either no records displaying or the 15,000. Can you please assist me with simplied yet detailed instructions? Thank You No, because you did not provide enough information: your table names and field names. We cannot see your database! Have you tried the "Unmatched Query Wizard" on the new query screen? That's just what it's designed to do. If that's not working for you please post back with the table and relevant fieldnames. John W. Vinson [MVP] |
#4
|
|||
|
|||
Query with three tables
On Fri, 18 May 2007 13:36:00 -0700, joey
wrote: Here Goes: Table 1 fields All Account Numbers All Account Names Table 2 fields: Account Numbers Account Names Cars (not yes or no) Table 3 fields Account Numbers Account Names Trucks (not yes or no) Unmatched Query Wizard results show me the account numbers, names and Cars or Trucks (like a "Yes" Result) I am looking for "orphan" Account Numbers and Account Names that are not associated with Cars/Trucks. Could the problem be that only Table 1 has all the Account Numbers/Account Names? I think the biggest problem is that Table 2 and Table 3 are incorrectly designed. You're essentially storing data - the existance of cars or trucks - in the table definition, rather than storing it as data! What is the *meaning* of these tables? What does it mean to have a record in Table2 with Cars set to Yes? What does it mean to have Cars set to No? Why would you store the account name (redundantly!) in both related tables, since you can find them by linking with the Account Number in Table1? Why not just have one table, with a yes/no field for Cars and another for Trucks? You can find all records in Table1 which have no matching account number in either Table2 or Table3 using a pair of Subqueries, but I think you may do better to properly normalize your data! Try SELECT * FROM [Table 1] WHERE NOT EXISTS(SELECT [Account Number] FROM [Table 2] WHERE [Table 2].[Account Number]=[Table 1].[Account Number]) OR NOT EXISTS(SELECT [Account Number] FROM [Table 3] WHERE [Table 3].[Account Number]=[Table 1].[Account Number]); |
Thread Tools | |
Display Modes | |
|
|