Need single report to pull from 2 queries

[Nathan Plemons]

I have 2 reports that use a separate query to pull their data. I'll lay it out.



One Query is called "Outdoor Lighting by Model Number Full Query"
It is very simple, pulling just 4 pieces of data, SQL code is as follows:

SELECT DISTINCT [Call Log].[Model #], Sum([Call Log].Quantity) AS [Sum Of Quantity]
FROM [Call Log]
WHERE ((([Call Log].Date) Between [Forms]![Date Range Entry]![Start Date] And [Forms]![Date Range Entry]![End Date]) AND (([Call Log].[Product Type])=4))
GROUP BY [Call Log].[Model #]
ORDER BY Sum([Call Log].Quantity) DESC;

I run a report named "Outdoor Lighting by Model Full Report" which takes the above query. The detail portion of the report only has 3 sections:
Model #, Quantity, Percentage. Quantity I have showing "Sum Of Quantity" and Percentage is showing "=[Sum of Quantity]/Sum([Sum of Quantity])"

Therefore, for a particular model # it gives me the number and then calculates what percentage that number is of the total. Works fine.

My second report is laid out exactly the same, except it pulls data from a different query that only returns the top five results. The code for that query is as follows:

SELECT TOP 5 [Call Log].[Model #], Sum([Call Log].Quantity) AS [Sum Of Quantity]
FROM [Call Log]
WHERE ((([Call Log].Date) Between Forms![Date Range Entry]![Start Date] And Forms![Date Range Entry]![End Date]) And (([Call Log].[Product Type])=4))
GROUP BY [Call Log].[Model #]
ORDER BY Sum([Call Log].Quantity) DESC;


Like I said the report is exactly the same, but I need to change it slightly and this is where I need the help. The second report, appropriately named "Outdoor Lighting by Model Number Top 5 Report" only shows the top 5 and then gives a quantity and calculates a percentage. My problem is that the percentage is calculated based off of the total of just the top 5 query. I need it to calculate based off of the total of the full query. If that sounds a little screwy, let me write it as an equation and see if that helps.

I currently have percentage doing this "=[Sum of Quantity(Top 5 Query)]/Sum([Sum of Quantity(Top 5 Query)])"
What I want it to do is this "=[Sum of Quantity(Top 5 Query]/Sum([Sum of Quantity(Full Report Query)])

It's an easy enough request, I just don't know if I can do it. Can anybody help me? I will state right off the bat that I am a complete novice in Access, everything I know about it I have learned from this project, so that's not much. The less complicated the solution the better.

Thanks in advance,

Nathan

[R_Badger]

It sounds like you need a DSUM (Domain Sum) that should give you the detail you need.

Check out http://msdn.microsoft.com/en-us/library/aa172193%28v=office.11%29.aspx

Don't be worried it says vba, but you can use it in a query, form/report control etc.

I would normally try to write it for you now, but I'm a little tired so I'll let you look first and if you still have trouble, post back saying so, and I will look with new eyes!

[Nathan Plemons]

Took me a few tries to get the syntax exactly the way it wanted, but that works beautifully! You made my day. If you ever need help with an LT1 engine, give me a yell, for Access though I'm mostly lost. Thank you so much!