Queries : 3-way relationship, causing duplicate entries (1 Viewer)

Hi everybody,

I know my way around Access reasonably well but am by no means an expert. I have created a system that I use in my business for hardware/software requests, and was told to do it in Access. I did ask that we do it on SQL Server with a Web Front End, but we are where we are.

Now, I have been on a SQL query training course so I know the basics, but am a bit confused on this one since Access has been added to the mix.

To make matters more frightening, this is Access 2003!

My tables are as follows:

[T_Request]
AT Reference (primary key, autonumber)
Forename
Surname
UserID, etc etc

[T_Hardwarelist]
ID (primary key, autonumber)
Make
Model
Description

[T_UserHardware]
AT Reference
ID


[Request] is inner joined to [UserHardware] on the AT Reference column.
[UserHardware] is inner joined to [Hardwarelist] on the ID column.

The select query I have is basic and does just what it says on the tin; it shows who has what hardware. Easy enough.

The query:

However, when it comes to adding NEW hardware to the request, things get a little sticky. I'll try to explain as best I can.

• I have a form called F_Request
• I have a sub form called SF_Hardware
• The SF_Hardware subform runs the aforementioned query, and shows what hardware is assigned to the parent request.
• If I add new hardware via a dropdown on the form, it adds it in to [T_UserHardware], but it also adds another value on the [T_Hardwarelist] table. E.g. if I add a "HP Compaq 8200 Elite" (which is stored in [T_Hardwarelist]) it adds it to the [T_UserHardware] table correctly, but creates a second (third, fourth, fifth) entry on the [T_Hardwarelist] table for the same kit.

I think its confusing because of using a select query and might have to run a 2nd query on save or something like that, but I'm not thinking clearly it seems and am a bit lost.

I had a working version before that had the make, model etc in both tables and didn't have a 3-way relationship, but that is a bit messy and I'm trying to learn on best practices etc. It'd be nice to have the request table with the high level info, a hardwarelist table with our catalog of kit, and a userhardware table just containing the ID and Reference for the hardware/request rather than duplicating the information.

I'm hoping I've been as clear as required, but I apologize if more info is needed. There is of course a distinct possibility that I am going about this entirely the wrong way due to being self-taught and rushing through for a deadline.

Thanks for your time

The main lesson here is that one form should be based on one table. Your subform should be based on T_UserHardware, not a query.

Once you do that, you will be back here saying, 'but I want Make/Model/Description in my drop down, not just an ID'. To do that, you make the combo box itself based on a query which is based on T_Hardwarelist and displays multiple columns from that query. If you search the forums, that issue has been solved many times.

Again, 1 form, 1 table.

EDIT:

Resolution was to remove the 3rd joined table (hardwarelist) from the query.
The Query the SubForm is based off is there because it needs to select only the applicable kit assigned to the request, not all kit assigned to any request. If I just made the table the form's data source it'd show any unless I used master/child links on the subform.

The combobox was already based off of another query from the hardwarelist table. Therefore, the only control that has been specified is the ID field on the form and the textboxes are set to a column selection of the main combobox.