HELP ASAP please

[Nadia]

Here's the background:

I have a table named "Inspections Data" and within the
table are the following fields: ID(unique #), Unit Number
(number that appears on the unit being inspected),
Employee ID (the inspectors assigned id #), Date (date of
inspection), and Code (inspection code). There may be
multiple codes entered for an id, in this case there are
two or more records with the same Id number and different
codes descripting the inspection results.

Within the table are regular inspection data and auditing
data. The auditing data will match with inspection data
by the unit number. (Auditors re-inspect units that were
already inspected to verify completeness.) The way to
tell if data is from an audit or an inspection is by the
Employee Id. Auditor is i.e. XX1234 and the id of an
inspector is i.e. 12345 (auditor's id always begins with
XX)

I'm attempting to prepare a query or queries that will be
used in a developing a report. I want the report to
layout unit that were audited per inspector. So I'd like
the unit number displayed then the inspector's id
followed by the date and corresponding codes then the
auditor's id followed by the date and corresponding codes.

PLEASE HELP...i have one together but keep getting
duplicate records. ANY SUGGESTIONS ARE MUCH
APPRECIATED!!!

Thanks in advance.

 

[Arvi Laanemets]

Hi

It is difficult to decide from your text, but it seems you don't use
advantages of relational databases. As much as I did understand, you need
several related tables to get your data properly organized. Let's have a try
(it's on fly, so you have to check it yourself).

The main event has to be an inspection. So the main table will be
Inspections: ID, UnitNum, EmployeeID, Date
(There will be a single row for every inspection, and no 2 rows have same
ID. Indexed are unique key field ID, and fields UnitNum and EmplyeeID)

The next table will contain inspection codes
InspectionCodes: RowID, ID, Code
(It will be the best, when you make the unique key field RowID to be an
Autonumber. ID must be of same format as ID in Inspections table, or long
integer when ID in parent table was autonumber. And of course RowID and ID
must be indexed.)

The next table
Audits: AuditID, ID, (EmployeeID), AuditDate
(AuditID as unique autonumber, ID as long integer, and EmployeeID are index
fields here.)

I think you need employee names, unit names, etc. in your report too. So I
think you'll want a couple of additional tables.

Units: UnitNum, UnitName, ...
Employees: EmployeeID, EmployeeName, ....

And maybe a table of codes too - it'll be handy when you decide you want to
use a combo to select the code.
Codes: Code, Explanation

Now you set relationships between your tables.
Units.UnitNum will be related to Inspections.UnitNum (all rows from
Inspections, and matching ones from Units)
Employees.EmployeeID will be related to Inspections.EmployeeID (all rows
from Inspections, and matching ones from Employees)
InspectionCodes.ID will be related to Inspections.ID (all rows from
Inspections, and matching ones from InspectionCodes)
Audits.ID will be related to Inspections.ID (all rows from Inspections, and
matching ones from Audits)

Codes.code will be related to InspectionCodes.Code (all rows from
InspectionRows, and matching ones from Codes)
Employees.EmployeeID will be related to Audits.EmployeeID (all rows from
Audits, and matching ones from Employees)

Now you can start wit your report.
You create a query, where you collect full data for every code, like this
ID, UnitNum, UnitName, EmployeeID, EmployeeName, Date, Code
and create a report based on this query, using grouping on ID
In group header you'll display all axept the code. The code will be place
into detail section

Create another query which contains a row for every audit
ID, AuditID, EmployeeID, EmployeeName, AuditDate
and create a report using grouping in ID, but leave group header empty. Into
detail insert EmployeeName and AuditDate

Now insert the second report into detail section of first one as subreport.
When it is done properly, you get the info about audits for every ID
displayed in parent report.