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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|