Final results

C

Charno

I'm a bit new to Access and trying to find my way around it.

I've created my tables and forms etc and i'm now trying to create reports
from the data that is now in the table.

I've created a query that does some calculations for me but i'm struggling
to get the information into the format / final results i want.

The query looks a bit like this:-

Date Name1 Name2 Name3 Name4 Name5 Name6 Name7 Name8 Rate
10/5 bob mike jim
10
10/5 mike bob
15
11/5 bill ted mike bob
10
11/5 jim ted bill bob fred dave zac
rob 20
11/5 bob
10


The final results i want would look like this:-

I want to apply a date filter (from to) eg

Between dates 10/5 to 11/5

Bob = 60
Mike = 35
Jim = 30
Ted = 30
bill = 30
fred = 20
dave = 20
zac = 20
rob = 20

Total = 285

Hope all that makes sence.......
Do i need to do another query to collate the names or is there a way to make
a report that wil do that?
 
C

Charno

Ooops that example looked right when i first typed it, looks a bit confusing
now lol

Basically it would be

Date Name1 Name2 Name3 >>>> to Name8 Rate
10/5 bob mike Jim >>>> 10

and so on.....

If that makes it clearer
 
J

John Spencer

It looks like you have a bad table design. If you have to work with that
structure you are going to need a union query to normalize the data and then
use the Union query to get your desired results. Union queries can only be
built in the SQL view.

SELECT [Date], Name1 as Worker, Rate
FROM [SomeTable]
UNION ALL
SELECT [Date], Name2, Rate
FROM [SomeTable]
UNION ALL
....
UNION ALL
SELECT [Date], Name8, Rate
FROM [SomeTable]

To get the final result you would use the above query as the source in a
totals (aggregate) query.
SELECT Worker, Sum(Rate) as Total
FROM [TheUnionQuery]
GROUP BY Worker

If you can only build queries in the query design view, post back and I will
try to lead you through building the Union query and the totals query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top