Guys need some query help...

[illy2k]

Ok, I have a form that lets someone enter Toll Receipts. The Form has a couple of fields, one being a combo box that looks up 10 toll types in another table, and about 4 other data entries such as date, time, amount, and the the toll number. This is all sent back to the same table, the "Toll Expense" table. I need to make a query, which takes each 10 of the toll types, and totals each individually for a certain date. So I made sub-queries, that filtered each toll type, so there were 10 queries. 1 for Ez-Pass, one for IPASS, ect. In each separate query I used the toll type name (i.e Ez-Pass, IPASS, ect.) to filter the query, putting it in the criteria section. Then I figured I could take the these 10 queries and make a final query. This would pull the date, and toll number from the "Toll Expense" Table and then the totals from each of the 10 sub-queries. However when I tried to do this through the wizard, it gave me an error, saying, "You have chosen fields from record sources which the wizard can't connect." I think it is a relationship problem. I need to somehow set the right relationship between the Table "Toll Expense" and all the sub-queries, I am not sure what though. I could use some help to tweek this. Thanks!

 

[Rich]

Use a Totals query, GroupBy DateField,TollType etc, Sum TollReceipts. That should give you the breakdown you want with one query

 

[illy2k]

Problem

When I do this, I keep getting duplicate dates in the query for some reason. I don't know why. It repeats dates and amounts in the final query for no reason.

 

[dcx693]

Can you post the SQL code of your query so we can take a look?

 

[illy2k]

Here is the SQL

SELECT [Toll Expenses].[TRANSACTION DATE], [EZ PASS Toll Query].AMOUNT, [IL TOLLWAY Toll Query].AMOUNT, [IPASS Toll Query].AMOUNT, [OH TURNPIKE Toll Query].AMOUNT, [OTHER Toll Query].AMOUNT, [T-CHEK Toll Query].AMOUNT
FROM ((((([Toll Expenses] LEFT JOIN [EZ PASS Toll Query] ON [Toll Expenses].TYPE = [EZ PASS Toll Query].TYPE) LEFT JOIN [IL TOLLWAY Toll Query] ON [Toll Expenses].TYPE = [IL TOLLWAY Toll Query].TYPE) LEFT JOIN [IPASS Toll Query] ON [Toll Expenses].TYPE = [IPASS Toll Query].TYPE) LEFT JOIN [OH TURNPIKE Toll Query] ON [Toll Expenses].TYPE = [OH TURNPIKE Toll Query].TYPE) LEFT JOIN [OTHER Toll Query] ON [Toll Expenses].TYPE = [OTHER Toll Query].TYPE) LEFT JOIN [T-CHEK Toll Query] ON [Toll Expenses].TYPE = [T-CHEK Toll Query].TYPE;


I haven't done the Nz() yet cause it is giving the multiple values. The reason I linked the table to each of the 6 queries is that I could do a left join to give me null values in the query.

 

[Jon K]

I think you can do it with a crosstab query (using the Crosstab Query Wizard) on the Toll Expense table, with TRANSACTION DATE as the Row Heading, TOLL TYPES as the Column Heading and Sum on AMOUNT.

 

[alpine82]

Thanks

The Crosstab was the way to go, the only thing is that for some reason the Nz() function is not converting null values to 0. Here look at the SQL.


TRANSFORM Sum(Nz(([Toll Expenses].AMOUNT),0)) AS SumOfAMOUNT
SELECT [Toll Expenses].[TRANSACTION DATE], Sum(Nz(([Toll Expenses].AMOUNT),0)) AS [Total Of AMOUNT]
FROM [Toll Expenses]
GROUP BY [Toll Expenses].[TRANSACTION DATE]
PIVOT [Toll Expenses].TYPE;

It still gives me blank values in the query, not 0.

 

[alpine82]

Got it

I got it to give a null value by putting the sum within the Nz function, i.e Nz((Sum([Toll].AMOUNT)),0).

However when I go into the query the numbers don't come out as Currency, even though the format of the field within the query was set to Currency format. Any suggestions?

 

[Rich]

Format(Nz((Sum([Toll].AMOUNT)),0),"Currency")

 

[alpine82]

Thanks!

That did it! Thanks!