By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,896 Members | 2,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,896 IT Pros & Developers. It's quick & easy.

Counting records in multiple tables

P: 5
Hello and Happy New Year,

I have two tables in Access 2003 (Windows XP) with similar, but different, information. Weíll call them table A and table B. I want to count the number of records in each table and display how many of each there are, and the total, by year:

2008 A B A+B
2007 A B A+B
2006 A B A+B

Right now I have a counting query for each of the tables, separately, but I canít figure out how to properly join them together. The problem is that B probably wonít have any records for 2008 for at least a few months (if ever), which also prevents the count of A from showing up if I try to do a query that relates the two tables by year. Is there a way to get around this? This is what I would want it to show:

2008 20 0 20
2007 60 4 64
2006 52 2 54
etc.

Iím happy to provide further details if you need them. Thanks!
Jan 3 '08 #1
Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,366
Use an outer join on the table that will have all the years you need.
Jan 3 '08 #2

P: 5
I had a feeling it was something completely simple that I just didn't know the term for. Thanks!
Jan 3 '08 #3

jaxjagfan
Expert 100+
P: 254
I had a feeling it was something completely simple that I just didn't know the term for. Thanks!

Or make a year table or query (Y) which will have all of the possble years listed in it and link A and B to Y (In the event individually neither A nor B will have all possibe years).
Jan 3 '08 #4

Rabbit
Expert Mod 10K+
P: 12,366
I had a feeling it was something completely simple that I just didn't know the term for. Thanks!
Not a problem, good luck. I would use jaxjagfan's idea of an extra table just to be on the safe side.
Jan 3 '08 #5

P: 5
An extra table is a good idea. But I seem to have made a mess of things somehow, in that when I try to make a new query, I get this error message:

You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query.

So I guess some details are in order. Bear with me; I'm a biologist and this database is for a project where we go out and trap invasive lizards (these lizards belong in Africa, they can get up to 7 feet long, and they're very efficient predators. They're really impressive creatures, but they might just eat up all our native wildlife if we don't stop them). People call us when they see one, and we go set a trap (I have a main table and form for Calls, then another table and subform for Trap_Info). Sometimes people catch these things in their own traps or they have one in their pool that we just go catch by hand. This data goes into tblTrapless_lizards, and that table (as well as tblTrap_Info) is linked to tblCalls. I'm keeping it separate from tblTrap_Info because there's a whole lot of information we need to keep track of when we actually set a trap (when did we put it out, when did we bring it back in, did we bring it in because it had been out too long or because we caught a lizard?, GPS coordinates, etc.) and all we really care about with the trapless lizards is getting them into the count.

Okay, relevant fields from tblTrap_Info:
Trap Event # - primary key, just an identifier
Date Deployed (mm/dd/yyyy)
Date Pulled (mm/dd/yyyy)
Lizard Caught - yes/no field, and only YESes are counted in my query
Call ID - links this to the Calls table (Call ID is the primary key in that table)
and lots of other fields

Relevant fields from tbltrapless_Lizards:
Lizard_ID - primary key, just an identifier
Date (mm/dd/yyyy)
and a few other fields, but if there's a record in this table, I want it counted.

I made queries to count the things I wanted from each table. They both have the same parts: a DatePart expression to pull out the year, and Count(*). The query for tblTrap_Info ("qrLizard_Count_ytd") also has a field requiring Lizard Caught to be TRUE. The other query is called "lizard_count_trapless". Please forgive the horrible naming conventions. Just in case you need it, here's the SQL:

For qrLizard_Count_ytd:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Count(*) AS [Count], DatePart("yyyy",[Date Pulled]) AS [Year]
  2. FROM tblTrap_Info
  3. GROUP BY DatePart("yyyy",[Date Pulled]), tblTrap_Info.[Lizard Caught]
  4. HAVING (((tblTrap_Info.[Lizard Caught])=True)); 
For lizard_count_trapless:
Expand|Select|Wrap|Line Numbers
  1.  SELECT Count(*) AS [Count], DatePart("yyyy",[date]) AS [Year]
  2. FROM tbltrapless_lizard_measurements
  3. GROUP BY DatePart("yyyy",[date])
  4. ORDER BY DatePart("yyyy",[date]) DESC; 
Now, I did get a query to work using an outer join based on Year: qrTest_Lizard_Count_total. The query takes the year from the trapped lizards info, since that's the vast majority of our catches and it's the other table that I'm worried about having a count of 0. Here's the SQL:

Expand|Select|Wrap|Line Numbers
  1.  SELECT qrLizard_Count_ytd.Year, qrLizard_Count_ytd.Count, lizard_count_trapless.Count
  2. FROM lizard_count_trapless RIGHT JOIN qrLizard_Count_ytd ON lizard_count_trapless.Year = qrLizard_Count_ytd.Year;
  3.  
I made up a new table, tblYEAR, and just put in years from 2000-2100. That's the only thing in the table, and it's the primary key. In the relationships window, I related that field to Year in both of the aforementioned queries.

Then I went into the query wizard and tried to select all those fields:
tblYear.year, qrLizard_Count_ytd.Year, qrLizard_Count_ytd.Count, lizard_count_trapless.Count
And that's when I got the error above.

Any thoughts? Am I making this too complicated?
Jan 3 '08 #6

Rabbit
Expert Mod 10K+
P: 12,366
Actually, I think this works without having to use an extra table.
Expand|Select|Wrap|Line Numbers
  1. SELECT qrLizard_Count_ytd.Year, qrLizard_Count_ytd.Count, lizard_count_trapless.Count
  2. FROM lizard_count_trapless RIGHT JOIN qrLizard_Count_ytd ON lizard_count_trapless.Year = qrLizard_Count_ytd.Year
  3. UNION
  4. SELECT lizard_count_trapless.Year, qrLizard_Count_ytd.Count, lizard_count_trapless.Count
  5. FROM lizard_count_trapless LEFT JOIN qrLizard_Count_ytd ON lizard_count_trapless.Year = qrLizard_Count_ytd.Year;
  6.  
Jan 3 '08 #7

P: 5
Ah, so that's what a union query is for. That works perfectly. And it's a small enough piece of code that I actually understand why it's working. Thanks again!
Jan 4 '08 #8

Rabbit
Expert Mod 10K+
P: 12,366
Not a problem, good luck.
Jan 4 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.