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 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
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:
Code:
SELECT T_UserHardware.[AT Reference], T_UserHardware.ID, T_HardwareList.Make, T_HardwareList.Model, T_HardwareList.Type, T_HardwareList.Description
FROM (T_Requests INNER JOIN T_UserHardware ON T_Requests.[AT Reference] = T_UserHardware.[AT Reference]) INNER JOIN T_HardwareList ON T_UserHardware.ID = T_HardwareList.ID
WHERE (((T_UserHardware.[AT Reference])=[Forms]![F_Request]![AT Reference]));
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