matt_m
asked on
Access Query Problem
Hi Experts
I'm a bit new to access so apologies in advance if this has a simple answer!
I have just set up a relational database in access consisting of four tables. Three of these tables consist of data with headings of an unique code, date and size. The fourth table has a main code and the matching code for each of the three tables so as to tie the three different codes in the three tables together (due to the source data they are different in all three tables although they refer to the same thing).
The problem is that since the three tables are not all complete sets when I run a query from excel, with the three tables linking to the reference table the only data that will show up is limited by the smallest data set. The fields that I want in the query are the main code from the reference table and then the amounts from each of the the data tables (if any) that link to that reference code.
Following this I was wondering if there was a way to structure a query of the four tables so all the data will show up even if there is an amount coming in from one table? I know I could add all the codes to each of the three data tables and put zeros in but would rather not do this.
Thanks!
I'm a bit new to access so apologies in advance if this has a simple answer!
I have just set up a relational database in access consisting of four tables. Three of these tables consist of data with headings of an unique code, date and size. The fourth table has a main code and the matching code for each of the three tables so as to tie the three different codes in the three tables together (due to the source data they are different in all three tables although they refer to the same thing).
The problem is that since the three tables are not all complete sets when I run a query from excel, with the three tables linking to the reference table the only data that will show up is limited by the smallest data set. The fields that I want in the query are the main code from the reference table and then the amounts from each of the the data tables (if any) that link to that reference code.
Following this I was wondering if there was a way to structure a query of the four tables so all the data will show up even if there is an amount coming in from one table? I know I could add all the codes to each of the three data tables and put zeros in but would rather not do this.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think you will have to post more detail on how you are doing this.
The message is wrong.
The message is wrong.
ASKER
I amanged to create the query graphically in Access (suing left joins) and then paste the SQL into excel and it worked fine. I think it may be a limitation of microsoft query in this regard to create graphically this type of query.
Thanks for your help Peter - the left join was right on the money!
Thanks for your help Peter - the left join was right on the money!
ASKER