Display additional field if multiple other fields match

[steed281]

I am building an inventory type database that compiles multiple items into a single item. The end result is a full inventory of an access control door. Each Door has an ID, each card reader has ID, each door position switch has an ID, each lock has an ID. Each peripheral device is linked to the Door ID to show which door the device is installed on. The issue I am having is when a door has multiple of a single device (ie. double door with two (2) door position switches). Each Door Position has its own ID even though they are on the same door. The ID numbers are compiled based on other information (CustomerID, SiteID, DoorID, DPSID). When I run query on all doors, the doors that have multiple Card Readers, multiple Locks, multiple Door Position Switches, the door is displayed twice with on the device ID of the multiple being the difference between the two. This makes it impossible to use the query to count how many doors we have.

Any suggestions?

[JoeM]

I think it would be very helpful if we could see a sample of what your data looks like and your expected result.
Can you post a sample of what the data looks like, and what the count you are hoping to return for that sample should be?

Also, can you post the SQL code for your existing query (switch the query to SQL View, and copy and paste the code here)?

[steed281]

The database is still quite small so I just attached a compressed version of it.

The SQL code is below just in case. If you need more, please let me know.

I appreciate the help.

SELECT qryAccessControlPanels.HeadEndLocation, qryAccessControlPanels.AccessControlPanelID, tblDoors.DoorID, tblDoors.DoorName, tblDoors.PortID, tblCardReaders.CardReaderPartNumber, tblDoorContacts.DoorContactPartNumber, tblLocks.LockPartNumber, tblLocks.SuppliedByDHPacePSI, tblLocks.InstalledByDHPacePSI, tblRequestToExits.REXPartNumber, tblRequestToExits.SuppliedByDHPacePSI, tblRequestToExits.InstalledByDHPacePSI, tblCardReaders.CardReaderID, tblDoorContacts.DoorContactID, tblLocks.LockID, tblRequestToExits.RequestToExitID
FROM ((((qryAccessControlPanels INNER JOIN tblDoors ON qryAccessControlPanels.AccessControlPanelID = tblDoors.AccessControlPanelID) LEFT JOIN tblCardReaders ON tblDoors.DoorID = tblCardReaders.DoorID) LEFT JOIN tblDoorContacts ON tblDoors.DoorID = tblDoorContacts.DoorID) LEFT JOIN tblLocks ON tblDoors.DoorID = tblLocks.DoorID) LEFT JOIN tblRequestToExits ON tblDoors.DoorID = tblRequestToExits.DoorID
WHERE (((tblDoors.DoorID) Is Not Null))
ORDER BY qryAccessControlPanels.HeadEndLocation, tblDoors.DoorID, tblCardReaders.CardReaderID, tblDoorContacts.DoorContactID, tblLocks.LockID, tblRequestToExits.RequestToExitID;

[steed281]

Sorry, I should have actually answered the first part of your post.

Right now there are only three (3) doors in the database. One (1) door 600024-001-AC001 is a double-door with two (2) door position switches (Door Contacts in DB) and two (2) different style card readers. Because of this, that door appears four (4) times in the query. I would like the door to appear just once with the 2nd Card Reader and the 2nd Door Contact listed in the same line if possible.

[JoeM]

The database is still quite small so I just attached a compressed version of it.

I am unable to download internet files from my current location, so I wouldn't be able to look at that until I am at home later tonight.

However, I think it is still important to see a small example along with your expected output, so we can fully understand the logic you want to use.

[steed281]

The expected output is a query to lists all of my Access Control Doors but I don't want duplicate DOor IDs listed. I need each Door ID to be listed once but if there are multiple of the same peripheral device are linked to any particular Door ID, I would like all of the peripheral devices to be included in the Door ID line.

[steed281]

Here's a screenshot of the query with enough fields hidden to show the issue. You can see four (4) door ID 600024-001-AC001. There are four (4) because it displays each time there is a single instance of a Card Reader and/or a Door Contact. You will notice the different Card Reader IDs and Door Contact IDs in each line.

[JoeM]

OK. So that is what is appearing now.
Based on those 6 records you are currently showing, what exactly do you WANT it to look like?
If you have to draw it up in Excel and paste the image here to show what you would like the final result to look like, that is fine.

[steed281]

Here you go.

Thank you.

[JoeM]

So, you want to create more fields going across for each of the multiple options?
The hard part about that is that you don't know how many duplicates you have, so you don't know how many extra fields you need.

I think the only way you might remotely be able to do something like that is with a CrossTab query, but I am not sure how to do that when they are multiple fields you want to duplicate.
You may want to Google CrossTab Query in Access. Here is one link: http://allenbrowne.com/ser-67.html

[orange]

steed,

My first thought when looking at your post and database is that you maybe dealing with a hierarchy or partial assembly. This is typical when you have individual parts/items; some of which can be combined to create a "new article/component", and still that article may be combined with parts/items or some sort of assembly to make yet another "article for sale".
In other words there are articles for sale and some are individual; some are combinations of things, and still others are multiple component assemblies which are sold as "manufactured unit".

The analogy is something along this line.
We manufacture integrated circuits and sub assemblies; some sub assemblies can be combined to make amplifiers; some ICs and subassemblies can be reconfigured and sold as a home theater system.

I'm not saying this is your situation, but it seems you are dealing with items/articles/products of various levels of assembly and combination.

There have been other posts where people build vehicles of various models and with various option packages.
Another example is people building various clothing articles for various audiences (child/teen/adult) and with various materials and accessories.

Good luck with your project.