Report problem or possibly db problem

[Guest]

I am at wits end. I have developed a db with three tables. The db is used
to log in employee training. First table is a list of all employee
information. 2nd table is the list of forms, work instructions, etc that
employees are required to be trained on. In the 2nd table there is a field
called "required" which I check when the item is required training. 3rd
table is where I actually log in the training that is completed, by employee.
This table contains the same required field, plus it also has the date of
training.
Now, I am trying to design a report which I can list all of the training
that is required for that employee, plus if he has been trained on it, it
would show the date of training. I have tried every way I know to complete
the report, but it is not pulling all of the rerods, or it repeats records
over and over to the tune of 47,000 records! I think I don't have some
fields set up right or possibly a relationship is wrong.
What I want to see is a report which shows a list of all of the training
that the employee is required to have, and then a checkmark and a training
date next to the ones that he has been trained on. That way we can quickly
look the report and tell what the employee has left to be trained on.
I have spent a month on this report and now it is down to the wire. I am
not very Access proficient (obviously), so any help would be appreciated.
Maybe you can tell me some things to check or change so that it works!

 

[Douglas J. Steele]

You need to create a query that joins the 3 tables in question, and returns
only the rows you want. Once you've got that query built, use it as the
recordsource for your report.

 

[Guest]

Hi Russel,

it repeats records over and over to the tune of 47,000 records!

This sounds to me like you may have a cartesian product query result. You
will get this in a query if you have more than one table, and there is no
join clause. For example, if 10 records are selected from Table A, and 20
records selected from Table B, a cartesian product will return 10 x 20 = 200
records.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I am at wits end. I have developed a db with three tables. The db is used
to log in employee training. First table is a list of all employee
information. 2nd table is the list of forms, work instructions, etc that
employees are required to be trained on. In the 2nd table there is a field
called "required" which I check when the item is required training. 3rd
table is where I actually log in the training that is completed, by employee.
This table contains the same required field, plus it also has the date of
training.
Now, I am trying to design a report which I can list all of the training
that is required for that employee, plus if he has been trained on it, it
would show the date of training. I have tried every way I know to complete
the report, but it is not pulling all of the rerods, or it repeats records
over and over to the tune of 47,000 records! I think I don't have some
fields set up right or possibly a relationship is wrong.
What I want to see is a report which shows a list of all of the training
that the employee is required to have, and then a checkmark and a training
date next to the ones that he has been trained on. That way we can quickly
look the report and tell what the employee has left to be trained on.
I have spent a month on this report and now it is down to the wire. I am
not very Access proficient (obviously), so any help would be appreciated.
Maybe you can tell me some things to check or change so that it works!

 

[Guest]

Russell:

These guys are both correct--your tables are not joined correctly, and as a
result you're getting every combination of records possible--like 47,000.

Visualizing the kind of report you're looking for, I think the best solution
would be a cross-tab query. It could list all of your employees down the
left side, all of the required courses across the top, and completion dates
within the matrix. Could look something like this:

Course 101 Course 102 Course 103
Tom 1/5/2005
Dick 1/5/2005 1/6/2005
Harry 1/5/2005 1/6/2005

All you'd need to do is play around with the Cross-tab Query Wizard. In the
Database Window, go to Queries, click New, and choose Cross-tab Query Wizard.
The tricky part is how the joins works, and honestly I can't look at my
Access now because of a program error, so I'd have to follow up later to work
that out for ya.

Good luck

Joe

 

[Guest]

Russell,

If a crosstab query will serve your needs, then you might want to try
working through a tutorial that I have prepared on the subject:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html

By the way, this document includes some coverage of cartesian products.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Russell:

These guys are both correct--your tables are not joined correctly, and as a
result you're getting every combination of records possible--like 47,000.

Visualizing the kind of report you're looking for, I think the best solution
would be a cross-tab query. It could list all of your employees down the
left side, all of the required courses across the top, and completion dates
within the matrix. Could look something like this:

Course 101 Course 102 Course 103
Tom 1/5/2005
Dick 1/5/2005 1/6/2005
Harry 1/5/2005 1/6/2005

All you'd need to do is play around with the Cross-tab Query Wizard. In the
Database Window, go to Queries, click New, and choose Cross-tab Query Wizard.
The tricky part is how the joins works, and honestly I can't look at my
Access now because of a program error, so I'd have to follow up later to work
that out for ya.

Good luck

Joe