Query or SQL code needed?

M

Mark

I have a table with a start date, start time, end date, end time, a
numeric points field, several event description fields which are
yes/no, and a participants field that is populated with ID number(s)
ex. 122, 158, 247, 266, 275

Using the ID number example above - I need to get a points total for 122
for each separate event. then the same for 158, etc.

I can send you a few records if you like to see if to make more sense -
thanks for ANY help.
 
J

John Vinson

I have a table with a start date, start time, end date, end time, a
numeric points field, several event description fields which are
yes/no, and a participants field that is populated with ID number(s)
ex. 122, 158, 247, 266, 275
Click to expand...

Do you have a Text field with participants separated by commas???

If so, your table design IS WRONG. Fields should be "atomic" - having
only one value.

If one Event can involve many People, the correct design is to have
TWO tables, in a one to many relationship; the second table would have
fields for the EventID and the ParticipantID, and if five people
participated you'ld have five records.
Using the ID number example above - I need to get a points total for 122
for each separate event. then the same for 158, etc.
Click to expand...

Not at all easily, since your table structure violates normal form.
I can send you a few records if you like to see if to make more sense -
thanks for ANY help.
Click to expand...

Post a couple of records here to clarify. If it's as you've described,
you'll need some VBA code to disentagle your data into a proper table.

John W. Vinson[MVP]
 
M

Mark

John said:
Do you have a Text field with participants separated by commas???
Click to expand...

Yes separated by commas --- -- since it'll help things I can modify it
to the proper design? Then I wouldn't have to wrestle with a way to get
something good out of a bad design.
If so, your table design IS WRONG. Fields should be "atomic" - having
only one value.

If one Event can involve many People, the correct design is to have
TWO tables, in a one to many relationship; the second table would have
fields for the EventID and the ParticipantID, and if five people
participated you'ld have five records.




Not at all easily, since your table structure violates normal form.




Post a couple of records here to clarify. If it's as you've described,
you'll need some VBA code to disentagle your data into a proper table.
Click to expand...
start date, start time, start month, start year, end date, end time,
end month, end year, total time, total points, event1, event2, event3,
participants (123, 287, 289, 266)

I'm not sure if this is sufficient - I can send you a smaller version
of the file if you want.
I'd like to end up with either a report within Access or export it to
Excel for the hard copy.

The report should have individual participants in the first column
followed by the total points for each event in the next columns.
Also - I'd like a Count of one of the events also for each participant.
Possible??? thanks
 
J

John Vinson

Yes separated by commas --- -- since it'll help things I can modify it
to the proper design? Then I wouldn't have to wrestle with a way to get
something good out of a bad design.
Click to expand...

GOOD thought. Yes, if you're going to do this in Access, work with
Access as it is designed rather than frustrating yourself by
struggling with a bad structure!
start date, start time, start month, start year, end date, end time,
end month, end year, total time, total points, event1, event2, event3,
participants (123, 287, 289, 266)
Click to expand...

Quite a few problems here!

I'd suggest reading some of the Database Design 101 links at Jeff
Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

For one thing, repeating fields like Event1, Event2, Event3 should
CERTAINLY be in a different table; for another, Access Date/Time
fields store the year, month, day, and time to the second all in one
field so it's neither necessary nor appropriate to have separate
fields for each. Does a StartTime refer to an event? or what Entity
(real-life person, thing, or event) is represented by this table?
Might Event1 and Event2 have different participants, or are they all
the same for a given record?
I'm not sure if this is sufficient - I can send you a smaller version
of the file if you want.
Click to expand...

Only if you want to set up a consulting arrangement. I volunteer my
time here for free, but fixing up databases and writing new ones is
what I do for a living.
I'd like to end up with either a report within Access or export it to
Excel for the hard copy.
Click to expand...

Access has a very capable report generator.
The report should have individual participants in the first column
followed by the total points for each event in the next columns.
Also - I'd like a Count of one of the events also for each participant.
Possible??? thanks
Click to expand...

Possible, even easy - IF you start with a properly designed set of
tables. Nightmarish if you start with a non-normalized, repeating
field, non-atomic spreadsheet!

Assuming that the Entities you're trying to model are Events,
Attendees, and Attendance, then I'd suggest tables like

Events
EventID <Primary Key>
EventStart Date/Time
EventEnd Date/Time
EventName
Points <assuming an event has a defined point value>
<other fields about the event as a whole>

People
PersonID
LastName
FirstName
<other appropriate biographical data>

Attendees
EventID <link to Events>
PersonID <link to People>
<any information about this person with regard to this event, e.g.
Role = Chair, Presenter, ...>



John W. Vinson[MVP]
 
M

Mark

John said:
GOOD thought. Yes, if you're going to do this in Access, work with
Access as it is designed rather than frustrating yourself by
struggling with a bad structure!




Quite a few problems here!

I'd suggest reading some of the Database Design 101 links at Jeff
Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

For one thing, repeating fields like Event1, Event2, Event3 should
CERTAINLY be in a different table; for another, Access Date/Time
fields store the year, month, day, and time to the second all in one
field so it's neither necessary nor appropriate to have separate
fields for each. Does a StartTime refer to an event? or what Entity
(real-life person, thing, or event) is represented by this table?
Click to expand...
Start time/date refers to each of the individual events. Some of the
events are: "community service", "administration", "vehicle
maintenance", etc.

Might Event1 and Event2 have different participants, or are they all
the same for a given record?
Click to expand...

Definitely - different participants for each event.
Only if you want to set up a consulting arrangement. I volunteer my
time here for free, but fixing up databases and writing new ones is
what I do for a living.
Click to expand...
Sorry - didn't mean to be off-base - still kind of learning Access and
newsgroup operation.
Access has a very capable report generator.




Possible, even easy - IF you start with a properly designed set of
tables. Nightmarish if you start with a non-normalized, repeating
field, non-atomic spreadsheet!

Assuming that the Entities you're trying to model are Events,
Attendees, and Attendance, then I'd suggest tables like

Events
EventID <Primary Key>
EventStart Date/Time
EventEnd Date/Time
EventName
Points <assuming an event has a defined point value>
<other fields about the event as a whole>

People
PersonID
LastName
FirstName
<other appropriate biographical data>

Attendees
EventID <link to Events>
PersonID <link to People>
<any information about this person with regard to this event, e.g.
Role = Chair, Presenter, ...>
Click to expand...
So if I have a current rec with 20 participants in the one field - the
attendees tbl will have 20 separate entries now?
EventID can be an abbreviation of the event name itself - if I'm
grasping this correctly?
 
J

John Vinson

Start time/date refers to each of the individual events. Some of the
events are: "community service", "administration", "vehicle
maintenance", etc.
Click to expand...

Then they need to be in the event table.

What Entity - real-life person, thing, or event - is represented by
your main table? You have stuff all over the map - you have a field
for Event1 and Event2 and Event3, but you have a Start Date and a
Start Time in THE SAME RECORD. That makes no sense; which of the three
events does it apply to!?
Definitely - different participants for each event.
Click to expand...

So: same problem. You have a list of participants in the same record
with Event1 and with Event2 and with Event3.
Sorry - didn't mean to be off-base - still kind of learning Access and
newsgroup operation.
Click to expand...

No harm, no foul... just making the groundrules clear.
So if I have a current rec with 20 participants in the one field - the
attendees tbl will have 20 separate entries now?
Click to expand...

Yes. Actually, the Attendees table will have probably many hundreds of
entries; for each Event it will have one record for each person
attending that event. "Records are cheap, fields are expensive" is an
old saying in this group!
EventID can be an abbreviation of the event name itself - if I'm
grasping this correctly?
Click to expand...

EventID should be the Primary Key of the Events table (every table
must have a Primary Key). The desiderata for a primary key are that it
should be UNIQUE - every event must have its own distinctive ID; it
should be STABLE - not something you'll edit often (or at all); and it
should preferably be SHORT, so that indexes using it will work
efficiently. It's not especially important that it be meaningful to a
human looking at the table! For instance, you might have an Autonumber
eventID; Event 32 might be the Administration event held on October
17, and Event 33 might be the Community Service event held on October
20. In a Form you'ld use a combo box to display the human-meaningful
title and date, while storing the computer-meaningful unique ID.

If you have multiple Administration events, then NO - calling all of
them "Admin" as the EventID would emphatically NOT work; that would
not be unique!

I really recommend reading some of the tutorials in Jeff's newsgroup,
posted upthread. A lot of this terminology will make more sense if you
do.

John W. Vinson[MVP]
 
Top