Prev: Reminder - Microsoft Responds to the Evolution of Community
Next: Merging Qry into Table
From: Charno on 20 May 2010 05:11

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: Alexander Achenbach on 20 May 2010 07:08

i would suggest to normalize your tables. With this data model you will
always run into troubles.

From: Charno on 20 May 2010 07:15
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.
>
From: KARL DEWEY on 20 May 2010 10:53
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.
> >
From: John W. Vinson on 20 May 2010 11:11
On Thu, 20 May 2010 02:11:01 -0700, Charno <Charno(a)discussions.microsoft.com>
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/accessjunkie/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]
 |  Next  |  Last
Pages: 1 2
Prev: Reminder - Microsoft Responds to the Evolution of Community
Next: Merging Qry into Table