Multiple unlinked queries, crashes when 1 query doesn't have a record 2

[d95bedw]

Using multiple queries to fill in the data for a report. Queries extract data from 1 table, they are based on dates. They give me the sum of people transported and the number of trips. I put all the tables into 1 query and added (query name)* to get all the fields available. There are no links between the queries, it should return 1 row of data with the sum and the count thats it. PROBLEM: Everything works fine if there is a record in each individual query, but 1 table doesn't have a record for this past month and I get no return on any of the queries. Anybody know of a way I can get the query to bring back a 0 for each of these fields? This way the data from the other queries would show up. I've searched around and can't find anything. My other problem is 1 of the other tables next month may not have a matching returned record.

 

[traingamer]

If you use a Left Join it should run whether or not any records are found in the joined table. If you want to post your query, I'm sure someone can help.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[d95bedw]

SELECT MEDICAL.*, [HALL OF JUSTICE TOTAL].*, [AIRPORT TOTAL].*, [POLICE ASSIST TOTAL].*, [MOTOR VEHICLE STOP TOTAL].*, [MILEAGE TOTAL].*, [TRANSPORTS FOR CCCF].*, [OTHER AGENCIES].*, OUTOFCOUNTY.*, [TRANSPORTS FOR MUNICIPALITIES].*, ARREST.*, *
FROM MEDICAL, [HALL OF JUSTICE TOTAL], [AIRPORT TOTAL], [POLICE ASSIST TOTAL], [MOTOR VEHICLE STOP TOTAL], [MILEAGE TOTAL], [TRANSPORTS FOR CCCF], [OTHER AGENCIES], OUTOFCOUNTY, [TRANSPORTS FOR MUNICIPALITIES], ARREST;

JUST THE GENERIC ADD SOME QUERIES TO THE DESIGN VIEW AND VIOLA. THERE ARE NO JOINS, EACH QUERY BRINGS BACK SEPERATE INFO SO I DON'T HAVE ANYTHING TO JOIN ON. THANK YOU FOR THE COMEBACK. %-)

 

[JonFer]

If each query returns a single record, then it's true you don't have to join them. However, if even one table has no records, then you'll get nothing. You need to modify your totals queries to always produce a result, even if it is zero. There are a couple of ways to do this depending on how you're totalling.


John

http://www.interautomateconsulting.com

 

[d95bedw]

NOW THATS WHAT I'M TALKING ABOUT. THATS EXACTLY WHAT KEEPS HAPPENING, AND IT MIGHT BE ANOTHER TABLE NEXT MONTH WHO KNOWS.I'M SURE THERE IS A SIMPLE THING I COULD DO, BUT I KEEP DRAWING A BLANK ON THIS. MY MIND IS FRIED. I'M GOING TO CHECK OUT THAT SIGHT AND I'LL GET BACK TO YA. THANKS AGAIN.

 

[d95bedw]

EACH RECORD IS CODED, QUERY ON THE CODE, SUM ON THE # OF BODIES AND COUNT ON THE AUTO ID NUMBER. GIVES ME SUMOFBODIES AND COUNTOFID AND THATS ALL I NEED TO FILL IN THE BLANKS FOR THE REPORT. VERY BASIC REPORT.

 

[d95bedw]

WHATS YOUR IDEA JOHN?

 

[lespaul]

[aside]using all capitals is consider shouting/yelling, please refrain from using all caps![/aside]

Can we see one of the queries that you are querying?

Thanks,


Leslie

 

[d95bedw]

THIS IS ONE OF THE QUERIES THAT IS PART OF THE ABOVE POSTED QUERY:
SELECT Sum(TRANSDATA.BODIES) AS SumOfBODIES, TRANSDATA.CODE, Count(TRANSDATA.ID) AS CountOfID
FROM TRANSDATA
WHERE (((TRANSDATA.DATE)>=[START DATE] And (TRANSDATA.DATE)<=[END DATE]))
GROUP BY TRANSDATA.CODE
HAVING (((TRANSDATA.CODE)="A"));
I WAS GOING TO TRY ASSIGNING A VALUE OF 0 TO VARIABLES AND PUT
THEM IN THE ON OPEN EVENT. NOT SURE HOW ACCESS WOULD LIKE ME TO REFERENCE A QUERY FIELD AS A VARIABLE.

 

[d95bedw]

SORRY BOUT THE WHOLE CAPS THING. LOOKS ALOT NEATER IN THE DB. ALRIGHT HOW ABOUT THIS:
DLookUp("SUM(NZ([BODIES],0))","[TRANSDATA]","

 = 'A'" & "AND [DATE]>=#2/1/05#" & "And [DATE]<=#2/28/05#")
BUT I STILL GET "NULL" IN THE IMMEDIATE WINDOW. IT'S BECAUSE THERE IS NO RECORD THERE. WORKS GREAT WHEN THERES A RECORD THERE WITH NO BODIES, WHEN THERE IS NO RECORD THATS THE PROBLEM. I WOULD BE HAPPY WITH N/A OR ANYTHING AT THIS POINT.

 

[PHV]

And this ?
Nz(DSum("BODIES","TRANSDATA","CODE='A' AND [DATE] Between #2/1/05# And #2/28/05#"),0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[d95bedw]

Perfect !!!!!!!!!!

Life is Good again. Thanks guys for all your help. Dean

 

[JonFer]

One thing you could do is create a query that always returns one record (Top 1 or some other method) and have a field in it that is always the value 1. Modify your other queries to have this field, too, which is always 1. Then do outer joins from the new query to all of the other queries. You'll always get a result and can use nz(Field,0) to get zero for any results from queries without any records.

This is probably more efficient than using DSum to do the totalling for the tables that may have no records selected, especially if they have a lot of records.

Also, you should modify your queries so that the HAVING criteria are moved to the WHERE clause. HAVING criteria should only be for aggregate functions like SUM(Field1)>0.


John

http://www.interautomateconsulting.com

 

[d95bedw]

There is only a couple queries that might not return records. The other queries will definitely return records, so on 3 of the text boxes on the report I used dsum and dcount. All this just to show a 0 on a report. Its all about the totals and seeing the numbers,thats all. I'll have to wait and see if it slows down with more records added. Thanks again you guys have been great, a real life saver. Dean