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:
-
SELECT DISTINCTROW Count(*) AS [Count], DatePart("yyyy",[Date Pulled]) AS [Year]
-
FROM tblTrap_Info
-
GROUP BY DatePart("yyyy",[Date Pulled]), tblTrap_Info.[Lizard Caught]
-
HAVING (((tblTrap_Info.[Lizard Caught])=True));
For lizard_count_trapless:
- SELECT Count(*) AS [Count], DatePart("yyyy",[date]) AS [Year]
-
FROM tbltrapless_lizard_measurements
-
GROUP BY DatePart("yyyy",[date])
-
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:
- SELECT qrLizard_Count_ytd.Year, qrLizard_Count_ytd.Count, lizard_count_trapless.Count
-
FROM lizard_count_trapless RIGHT JOIN qrLizard_Count_ytd ON lizard_count_trapless.Year = qrLizard_Count_ytd.Year;
-
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?
5
