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?

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

"Charno" wrote:

> 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?
> 
> 
> 
> 

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

Charno wrote:
> 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?
> 
> 
> 
>