Trouble combining results from multiple queries

[jvanderw]

Group,
I have a somewhat complex problem to explain so bear with me. I have a table called "TallyName" that shows every tally name that can occur in my database (each tally name occurs only once). I have one query called "CurrentTallies" that sums the number of tallies by tally name and source code (source code is a broader description of the type of tally: each tally can occur within each source code) for the most recent 12 month period. I have another query called "PreviousTallies" that sums the number of tallies by tally name and source code for the previous 12 month period. (Example: "PreviousTallies" for 1/1/02-12/31/02 and "CurrentTallies" for 1/1/03-12/31/03)
I am trying to create a query called "AllTallies" that will show the 10 tallies that occurred the most within the last 12 months, and then show how often they occurred in the previous 12 months. The way I thought this query should work is group by field "tally name" from table "TallyName" and sum the number of tallies from each of the 2 other queries in 2 other fields. I set up join properties to include all records from table "TallyName" and only those records from the other queries where the joined fields are equal.
When I run the query I get 9 times the data that I should. I think that this is because there are 9 different source codes, but don't know why it behaves this way. I thought that since I was grouping by tally name that it wouldn't matter how many times a tally name appears in the "PreviousTallies" and "CurrentTallies" queries. I have set up the join properties every way possible, but it doesn't seem to make any difference in the results.
I know that I could group the info without the source code in the "PreviousTallies" and "CurrentTallies" queries, but I will also be running queries similar to "AllTallies" that limit the results to only certain source codes and want to use the same source queries if at all possible.
Does anyone have any ideas?

Josh

 

[hneal98]

Even though you gave a long explanation, it would be easier if we could see the queries you are working on and see some of the data, as well as an example of what you want the results to look like.

 

[sillysod]

well to get the top ten "tallys" just set to sort of the tally field to descending and then limit the 'max records' property of query to 10. (right click in grey area and select properties)

Now i dont know of a way to do this, as an arse upwards way though you could set up a query to get the top ten tallies of the current year.

and then a separate query to get the totals for tallies in previous year.

Now put the original query on a report and call your tally totals control fields tally10 tally 09 etc

now in your second query set the criteria for the tally to be =reportbleh.tally10.value OR =reportbleh.tally09.value etc etc
(ORs in query builder list down wards under a field)

then put the results of that query on your report.

What im trying to achieve there (gonna talk generally now, say sales of a particular item)

get the top 10 selling items for the current year and there sales figure and put this in a report, calling the controls that the items are going into 10thItem 09thItem etc etc

then query your other table where the criteria is that the Item is = to any of the items in previous query, give it a total and slap that next to it on your report.


Thats a very very roundabout way to go, but if your stuck and its urgent it might just work.



AHHHA inspiration, create your first query for the current tallies, then create another query based on that query and your prev tallies table. create a link between your tally field in the query and tally field in the prev tally tabl.
Create the link betwenn them as option 1 (include all records from your query and only those in table which match)

add the sum field and hey presto