Duplicated data in a report

  • Thread starter Thread starter Brotherwarren
  • Start date Start date
B

Brotherwarren

Hi folks,

I have re-read my earlier post asking for assistance with a report.
It made no sense to me, so I'm not surprised that no-one replied.

I'll have another go:


We maintain a database where local firms offer work experience to
pupils at our school. Once per year students choose three firms that
they are interested in working for.
I need to see a report that shows for every firm, which pupils have
selected it as a first choice, which have it as a second choice and
which as a third.


Here are the structures of my tables:


Employers Jobs
offered Pupils
==================================================================================
RefID------linked-------------------Ref
ID PupilID
Name JobID-----------
linked----------------First choice

----------------Second choice

----------------Third choice


Pupils choose a job by writing the JobID in their first, second or
third choice.
Some employers offer more than one job.



Via a query I can produce a report that shows for each job, who has
selected it as a first choice.

Whenever I try to produce a report that shows for each job, who has it
as a first choice and who as a second, I get lots of data repeated.
I've tried editing the textbox's properties in the report to remove
duplicates, but they still appear.



As said, I'm a beginner, I can produce this in Excel quiet easily, but
it runs slowly.
Someone suggested using access could speed it up?
Am I asking Access to do something impossible?

Please help, I've reached, and passed the shouting at the monitor
stage!

Cheers

Tony
 
Your attempt to describe your table structure didn't format very well. I
think you need to normalize your tables like:

tblEmployers (one record per employer)
=========================
EmployerID PK
....

tblEmployerJobs (1 record per job per employer)
==========================
EmpJobID PK
EmployerID FK to tblEmployers.EmployerID
JobTitle
JobCount
....

tblStudents (1 record per student)
===========================
StudentID PK
.....

tblStudentJobs (1 record per student per choice)
==========================
StudJobID PK
StudentID FK to tblStudents.StudentID
EmpJobID FK to tblEmployerJobs.EmpJobID
ChoiceNumber 1, 2, 3, or ...
StudJobStatus

This structure should provide the greatest flexibility for reporting. If you
have a non-normalized table for student choices, you can create the
normalized table using a union query.
 
Back
Top