Report based on Query

[Anthonydb]

I don't know if this should be in queries or in reports but here goes. I have a report which is based on a query and the query is based on 3 tables. My problem is there are multiple records for individuals that I wan't to appear but there is certain data I want to appear only once.

Example:

John, Jane and Nancy all have multiple pieces of computer hardware ie, laptop, desktop and printers. They also each have 1 PDA, be it a Blackberry or Palm Pilot. When the report is run John will have multiple rows of data identifying his desktop as 1 record, laptop as the 2nd record and printer as the 3rd record, which is what I want. But, whichever PDA John has will appear on the 1st, 2nd, and 3rd record also. For each person I would like the PDA to appear the 1st time and only time. I hope this makes sense.

Sorry about the length, any all suggestions are welcomed. I've searched for criteria to implement in query and either I'm not using the right criteria or the only way to do it is with code. I'm not to skilled in the coding department so assistance in that area would have to be indepth. Thanks for the assistance in advance.

 

[Pat Hartman]

You are attempting to create a query from tables that have no relationship to each other. The employee table has a 1-to-many relationship to the hardware table and the employee table has a 1-to-many relationship to the PDA table. But the hardware table and the PDA table have no relationship to each other. I'm not sure why the PDA is in its own table. Why is it not just another type of hardware?

In any event, to get any meaningful results from all three tables, you'll need a union query. To begin, make two queries. One that joins employee to hardware and the second that joins employee to PDA. Then union those two queries.

Select * From qryHardware
Union Select * From qryPDA;

 

[Anthonydb]

Here is my data:

[Employee ListingTable]

Id LastName FirstName

[Locations Table]

Type Manfu Model RAM Speed HDC

[Handheld Configuration]

HHD_ID Handheld Model SW Type SW Version

These tables are combined as a Select Query.
The problem is that John Doe will have the following rows of data;

Type Manfu Model RAM Speed HDC Handheld Model SW Type SW Version
Desktop Dell GX200 256 866 10Gig Blackberry 957 Blackberry 3.12
Laptop IBM T20 256 1.2GHz 30Gig Blackberry 957 Blackberry 3.12
Laptop IBM X30 256 1.5Ghz 40Gig Blackberry 957 Blackberry 3.12
Desktop Dell GX200 256 866 10Gig Blackberry 957 Blackberry 3.12

I don't understand how the previous response will solve my problem. Each individual has multiple computer equipment but has only 1 PDA. They have either a Blackberry or Palm Pilot.

Although an individual can have 4 or 5 records, no individual has more than 1 PDA, hence the problem. I want all the records to appear but I want that 1 data element (Handheld) to be restricted to the first applicable record.

Of the 4 of 5 records, the first record should have Blackberry and all corresponding information. The records thereafter should be blank fields as they relate to all Handheld information.

I hope this information helps to make better sense of this problem.

 

[Pat Hartman]

I did understand your problem. It is exactly as I said it was. The table relationships are not hierarchial. Two of the tables have no relationship to each other. You CANNOT make a meaningful query from tables that have no relationship. Just because the hardware and PDA tables have a foreign key in common (employeeID), does not mean that they have any relationship with each other so don't even think about defining a relationship in the relationship window. That is not the problem.

You can show the information you want by making a report with two subreports. The main report is based on employee data, one subreport shows hardware and the other subreport shows PDA data. You could do the same thing with a main form and two subforms.

Queries/tables do not work the way you think. They are not spreadsheets where individual cells can be addressed and have their own attributes.

 

[Anthonydb]

Thanks for the help but I found a very simple solution. I have the data elements as Text Boxes on the report and all I had to do was open the properties box and click on Hide Duplicates for each text box that I don't want to repeat. Sometimes things are so easy and right under our noises we think that it has to be harder then it actually is. Again thanks for the help.