Duplicated data in a report
in [Reports]
|
From: Brotherwarren on 27 Jun 2008 03:53 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
From: Duane Hookom on 27 Jun 2008 11:32 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. -- Duane Hookom Microsoft Access MVP "Brotherwarren" wrote: > 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 >
|
Pages: 1 Prev: New Version ConvertReportToPDF, Page Size Bug Next: VBA to output repor to PDF |