Question on getting information from two tables (1 Viewer)

[drscot19]

I have 2 tables with a total of 80 records. Table one has a one-2-many relationship with table two based on the social security number

tblOne - Name, Address, Phone, and Social Security Number.

tblTwo - Social Security Number, Account Name


How do I get a list of all the name, address, phone and social security numbers where account equals “bank account”? And say if out of the total 80 records only 60 have an account called “bank account” how can I also get the other 20 that do not have “bank account” listed?


TIA,

 

[pbaldy]

Set up a query with both tables in design view. Make sure the join appears between the 2 SSN fields. In addition to the fields you want to see returned, add the account name field to the list of fields (you can uncheck the box so it doesn't show). In the criteria row for that field, put “bank account”. You should get the 60 with “bank account” in that field. To get the other 20, put <>“bank account” in the criteria.

 

[drscot19]

Hello Paul and thank you for replying.

I tried it but now what I am getting is a list of all users. For example

name address ss account
Bill Miller 111335555 bank account
Bill Miller 111335555 credit account
Cheryl Scott 222668989 brokerage account
Cheryl Scott 222668989 credit account
Randy Maker 555449999 bank account
Randy Maker 555449999 brokerage account
Randy Maker 555449999 credit account
Tom Jones 111223333 bank account
Tom Jones 111223333 brokerage account
Tom Jones 111223333 credit account

What I would like to get is this.

name address ss account
Bill Miller 111335555 bank account
Cheryl Scott 222668989
Randy Maker 555449999 bank account
Tom Jones 111223333 bank account


I was not really sure how to ask this question so I hope this helps. Cheryl does not have a bank account so I would still like to display her record but maybe indicate that that she has no bank account. I hope I am makeing sense here, if you have any questions feel free to ask.

Thanks again..

 

[pbaldy]

Can you either post a sample db with the query and table, or at least post the SQL view of the query? If the query includes something like:

WHERE Account = "Bank Account"

you should only get those records that have that, not all records.

 

[drscot19]

Hello Paul,


Here is the SQL

SELECT tblOne.name, tblOne.address, tblOne.ss, tblTwo.account
FROM tblOne LEFT JOIN tblTwo ON tblOne.ss = tblTwo.ss
GROUP BY tblOne.name, tblOne.address, tblOne.ss, tblTwo.account
HAVING (((tblTwo.account)="bank account")) OR (((tblTwo.account)<>"bank account"));


If I take out the "OR (((tblTwo.account)<>"bank account"))" then I get only the records that have "Bank Account" listed. But How would I get the other prople that do not have a "Bank Account" and just list them once?


Thanks

 

[pbaldy]

I guess I misunderstood your original question; I thought you wanted 2 different queries. This is going to give you all records, since you've basically said "give me everything that has "bank account" and everything that doesn't:

HAVING (((tblTwo.account)="bank account")) OR (((tblTwo.account)<>"bank account"));

If you want a list of the people, and whether or not they have a bank account, this will work (you can change the descriptions to whatever you want):

SELECT DISTINCT name, address, ss, IIf(DLookUp("account","tblTwo","account = 'bank account' AND ss = " & [ss]),"Has one","doesn't") AS BankAccount
FROM tblOne;

I suspect there's a way to do it without the dlookup, but it's not coming to me right off, and I wanted to get you something that worked. I put your sample data above into a db and the above query returned:

 

[drscot19]

Hello Paul,


Thanks for the help!! I am getting the error message "Data Type Mismatch in criteria expression" when I try to run the query. Any ideas?


Thanks

 

[pbaldy]

If the SS field is text rather than a number, it would look like this:

SELECT DISTINCT name, address, ss, IIf(DLookUp("account","tblTwo","account = 'bank account' AND ss = '" & [ss] & "'"),"Has one","doesn't") AS BankAccount
FROM tblOne;

Note the single quote after the second "=" and the "&" with double-single-double quotes after [ss]. I had it as a numeric field in my testing, and text fields are treated differently in the dlookup formula.

 

[drscot19]

Hello Paul,


Thanks for the help!! I am getting the error message "Data Type Mismatch in criteria expression" when I try to run the query. Any ideas?


Thanks

 

[drscot19]

opps! Didn't mean to post the last reply again. Thanks again for all the help Paul. I will give it a try and post back..

 

[drscot19]

Well that did it alright! Thanks again for the help, love this forum. I really have learned alot from just searching and reading all the posts. I still kind of get confused on the single quotes and double quotes but I'll get it at one point!

 

[pbaldy]

Here's a good reference for quote usage in dlookups, and the general idea carries over into other areas as well:

http://www.mvps.org/access/general/gen0018.htm