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? 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 |
#2
|
|||
|
|||
Final Results
i would suggest to normalize your tables. With this data model you will always run into troubles. |
#3
|
|||
|
|||
Final Results
I'm not quite sure what you mean? sorry i'm new to access and trying to learn
as much as i can "Alexander Achenbach" wrote: i would suggest to normalize your tables. With this data model you will always run into troubles. |
#4
|
|||
|
|||
Final Results
You have a spreadsheet, not a relational database.
Your table should be like this -- Date Name Rate The data would look like this -- 5/1/2010 bob 10 5/1/2010 mike 10 5/1/2010 jim 10 5/1/2010 bob 15 5/1/2010 mike 15 Use a union query to fix the data -- SELECT Date, Name1 AS Name, Rate FROM your Table UNION ALL SELECT Date, Name2 AS Name, Rate FROM your Table WHERE Name2 Is Not Null UNION ALL SELECT Date, Name3 AS Name, Rate FROM your Table WHERE Name3 Is Not Null UNION ALL SELECT Date, Name4 AS Name, Rate FROM your Table WHERE Name4 Is Not Null ....... UNION ALL SELECT Date, NameX AS Name, Rate FROM your Table WHERE NameX Is Not Null; Also date and name are reserved words and may give you trouble in some queries, forms, and reports. -- Build a little, test a little. "Charno" wrote: I'm not quite sure what you mean? sorry i'm new to access and trying to learn as much as i can "Alexander Achenbach" wrote: i would suggest to normalize your tables. With this data model you will always run into troubles. |
#5
|
|||
|
|||
Final Results
On Thu, 20 May 2010 02:11:01 -0700, 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 If this reflects the structure of your table, it's just as Alexander says: your table structure is WRONG. What will happen when you have a ninth name? Redesign your table, move all your data, create new forms, new reports, new queries!? If each Date involves multiple Names, and each name may deal with multiple dates, you need *three tables*: People PersonID primary key FirstName LastName other biographical info; don't use Name as a fieldname or rely on people's names to be unique, because they're not Events or Workdays or whatever your table represents EventID primary key EventDate don't use Date as a fieldname, it's also a reserved word like Name Rate PeopleInvolved EventID link to Events, to pick up the date and rate PersonID link to People, to connect to their name any other info about this person's connection to this date, if any You may want to check out some of these resources; Crystal's tutorial has a page on "Normalization" that may help clarify things: Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Final Results
"Charno" schrieb im Newsbeitrag ... 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 |
#7
|
|||
|
|||
Final Results
Charno wrote:
I'm not quite sure what you mean? sorry i'm new to access and trying to learn as much as i can i would suggest to normalize your tables. With this data model you will always run into troubles. John's right... I had to deal with databases that were structured in the way you're proposing. In a word, *very* bad idea. Summarizing the data or finding records is a complete nightmare. Don't take my word for it, though. Make a nice table with maybe 50 records. Then do like 5 union queries on it. Then sort/filter that. It takes FOREVER because you can't use any of the indexing (the UNION statement causes them all to be ignored), so your queries will be painfully slow. And forget about expanding your database. The only reason I say anything at all is that I had to work with designs like this for 10 hours a day for like 3 months. It was absurd. Took forever to do anything. Do yourself a favor and design right from the beginning (build with the end in mind) and your job will be MUCH easier and your design more flexible. Post your next design. (Well, feel free to play with a messed up design for a little while just to get that bad idea out of your system... then come back and ask more questions). -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|