A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Final results



 
 
Thread Tools Display Modes
  #1  
Old May 20th, 2010, 08:47 AM posted to microsoft.public.access.reports
Charno
external usenet poster
 
Posts: 20
Default Final results

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?




  #2  
Old May 20th, 2010, 09:05 AM posted to microsoft.public.access.reports
Charno
external usenet poster
 
Posts: 20
Default Final results

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?




  #3  
Old May 20th, 2010, 02:21 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Final results

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?




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:27 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.