Table design

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

I am tracking data by date that may have up to 20 seperate cominations and
all using a drop down list. Would I need 20 different data fields or is their
another way.
if that is the case I would have to use the same drop down list for every
data field. I will be using this information to construct a cross tabe feld.
Am I on the right track or should I do something else.

Thanks in advance
 
I am tracking data by date that may have up to 20 seperate cominations
Click to expand...

Could you explain what a "comination" is? The word's not in my dictionary.
Typo for "combination"? if so, what is combined with what?
and
all using a drop down list. Would I need 20 different data fields or is their
another way.
Click to expand...

20 fields would be a spreadsheet, and NOT a rational design for a table.
if that is the case I would have to use the same drop down list for every
data field. I will be using this information to construct a cross tabe feld.
Am I on the right track or should I do something else.
Click to expand...

You are on the wrong track, but since I don't have any clear idea where you're
trying to go, I'm not sure I can recommend a right track! Could you post an
example of the data, and some more description of the cominations logic?

John W. Vinson [MVP]
 
Nick

You've described "how" you are trying to do something.

I don't have a very clear picture of "what" that is.

Are you saying that there are up to 20 different "dates" connected with the
thing you are tracking?

If so, how is your data (tables) structured? If you have (up to) 20 fields,
you have a ... spreadsheet!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thank you for reviewing this. The data in the drop down list (InfractionLis)
consist of 20 catagory some of which are "No Violation","Turn signal", "Yield
right of way" and "Curb wheels" to list a few. A person may violate some or
all of the catagory with the exception of "No Violation" It is the catagories
violated I am to track for each employee [EmplID] whether it is one or many.
The other problem to over come is the report. It would be a crosstab query
showing the sum of violations for each catagory monthly.
I said:
I am tracking data by date that may have up to 20 seperate cominations
Click to expand...

Could you explain what a "comination" is? The word's not in my dictionary.
Typo for "combination"? if so, what is combined with what?
and
all using a drop down list. Would I need 20 different data fields or is their
another way.
Click to expand...

20 fields would be a spreadsheet, and NOT a rational design for a table.
if that is the case I would have to use the same drop down list for every
data field. I will be using this information to construct a cross tabe feld.
Am I on the right track or should I do something else.
Click to expand...

You are on the wrong track, but since I don't have any clear idea where you're
trying to go, I'm not sure I can recommend a right track! Could you post an
example of the data, and some more description of the cominations logic?

John W. Vinson [MVP]
Click to expand...
 
Thank you for reviewing this. The data in the drop down list (InfractionLis)
consist of 20 catagory some of which are "No Violation","Turn signal", "Yield
right of way" and "Curb wheels" to list a few. A person may violate some or
all of the catagory with the exception of "No Violation" It is the catagories
violated I am to track for each employee [EmplID] whether it is one or many.
The other problem to over come is the report. It would be a crosstab query
showing the sum of violations for each catagory monthly.
Click to expand...

Then you have a Many (employees) to Many (violations) relationship. The proper
way to model this is with three tables: Employees, with primary key EmplID and
biographical data; Violations, just the list of violations (perhaps with other
fields such as severity, e.g. Vehicular Assault might be a firing offense,
while Curb Wheels wouldn't); and EmployeeViolations, which I'd suggest should
have fields EmplID (a link to Employees), Violation (a link to Violations),
IncidentDate (or perhaps IncidentDateTime), and Comments.

Rather than twenty fields you would enter zero, one, sixteen, or sixty
*records* in this latter table, using a Subform. I'd leave out "No Violation"
since it is redundant - just doing a search and finding zero matches in the
EmployeeViolations table implies... well... no violations.

You can certainly base a Crosstab query on this table for the report; join the
table to Employees, use the EmplID and the employee name as Row Headers and
the violation as the Column Header.

John W. Vinson [MVP]
 
To repeat what I understatnd. Basicly I would have[EmplID] as (primary key)
with the date of the violation [VioDate] and then the singular violation
[ViolatAct] and then a field with comments [ComofAct]. With that in mind how
would I record employee xyz on 12/04/07 having multiple violations as
curbing wheels, vehicle not locked and no seat belt. in the [ViolatAct]?.
Then how would I build the cross tab to calulate total each catagory? The
report for this data in in an Excell spread sheet and I currently have to
calculate each catagory manualy. I apologize I am having some difficulty
with the multiple violation entry. Thank you for patience."John W. Vinson"
wrote:
Thank you for reviewing this. The data in the drop down list (InfractionLis)
consist of 20 catagory some of which are "No Violation","Turn signal", "Yield
right of way" and "Curb wheels" to list a few. A person may violate some or
all of the catagory with the exception of "No Violation" It is the catagories
violated I am to track for each employee [EmplID] whether it is one or many.
The other problem to over come is the report. It would be a crosstab query
showing the sum of violations for each catagory monthly.
Click to expand...

Then you have a Many (employees) to Many (violations) relationship. The proper
way to model this is with three tables: Employees, with primary key and
biographical data; Violations, just the list of violations (perhaps with other
fields such as severity, e.g. Vehicular Assault might be a firing offense,
while Curb Wheels wouldn't); and EmployeeViolations, which I'd suggest should
have fields EmplID (a link to Employees), Violation (a link to Violations),
IncidentDate (or perhaps IncidentDateTime), and Comments.

Rather than twenty fields you would enter zero, one, sixteen, or sixty
*records* in this latter table, using a Subform. I'd leave out "No Violation"
since it is redundant - just doing a search and finding zero matches in the
EmployeeViolations table implies... well... no violations.

You can certainly base a Crosstab query on this table for the report; join the
table to Employees, use the EmplID and the employee name as Row Headers and
the violation as the Column Header.

John W. Vinson [MVP]
Click to expand...
 
To repeat what I understatnd. Basicly I would have[EmplID] as (primary key)
with the date of the violation [VioDate] and then the singular violation
[ViolatAct] and then a field with comments [ComofAct]. With that in mind how
would I record employee xyz on 12/04/07 having multiple violations as
curbing wheels, vehicle not locked and no seat belt. in the [ViolatAct]?.
Then how would I build the cross tab to calulate total each catagory? The
report for this data in in an Excell spread sheet and I currently have to
calculate each catagory manualy. I apologize I am having some difficulty
with the multiple violation entry. Thank you for patience
Click to expand...

This table would NOT have EmplID as the Primary Key (otherwise you could enter
one and only one violation per employee, ever). EmplID would be a non-unique
*foreign key* to the separate employee table.

For multiple violations you would have multiple rows. If employe xyz has 315
as his EmplID, you would have records like

315; #12/4/2007#; "Curbing Wheels"; Null
315; #12/4/2007#; "Curbing Wheels"; "violated again after going to lunch"
315; #12/4/2007#; "Vehicle Not Locked"; Null
315; #12/4/2007#; "No seat belt"; Null
315; #12/4/2007#; "Insubordination"; "mouthed off to and threatened parking
inspector on being warned for third time"

The Crosstab query would have the Employee table joined to the
EmployeeViolations table by EmplID; you would use the employee ID, name, and
other employee information as Row Headers (to get one row per employee), and
the ViolAct as the column header, with the count of records as TheValue.

John W. Vinson [MVP]
 
Thank you, I now see what you mean. It should not be difficult to set up.
Thanks again.

John W. Vinson said:
To repeat what I understatnd. Basicly I would have[EmplID] as (primary key)
with the date of the violation [VioDate] and then the singular violation
[ViolatAct] and then a field with comments [ComofAct]. With that in mind how
would I record employee xyz on 12/04/07 having multiple violations as
curbing wheels, vehicle not locked and no seat belt. in the [ViolatAct]?.
Then how would I build the cross tab to calulate total each catagory? The
report for this data in in an Excell spread sheet and I currently have to
calculate each catagory manualy. I apologize I am having some difficulty
with the multiple violation entry. Thank you for patience
Click to expand...

This table would NOT have EmplID as the Primary Key (otherwise you could enter
one and only one violation per employee, ever). EmplID would be a non-unique
*foreign key* to the separate employee table.

For multiple violations you would have multiple rows. If employe xyz has 315
as his EmplID, you would have records like

315; #12/4/2007#; "Curbing Wheels"; Null
315; #12/4/2007#; "Curbing Wheels"; "violated again after going to lunch"
315; #12/4/2007#; "Vehicle Not Locked"; Null
315; #12/4/2007#; "No seat belt"; Null
315; #12/4/2007#; "Insubordination"; "mouthed off to and threatened parking
inspector on being warned for third time"

The Crosstab query would have the Employee table joined to the
EmployeeViolations table by EmplID; you would use the employee ID, name, and
other employee information as Row Headers (to get one row per employee), and
the ViolAct as the column header, with the count of records as TheValue.

John W. Vinson [MVP]
Click to expand...
 
Back
Top