From: bernd on 23 May 2010 21:58 "Charno" <Charno(a)discussions.microsoft.com> schrieb im Newsbeitrag news:0789C666-10E1-436A-94AC-23867B9D5651(a)microsoft.com... > > 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 >
From: PieterLinden via AccessMonster.com on 24 May 2010 00:41 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
First
|
Prev
|
Pages: 1 2 Prev: Reminder - Microsoft Responds to the Evolution of Community Next: Merging Qry into Table |