Jul 20 2021 10:18 AM
I am creating an Inventory Database for out IT equipment and I am still learning Access (I have used it a little in the past, but that was years ago, so I have been taking a course on CBTNugget as I create this database and my SQL knowledge is still a little limited).
The data is being imported from the Excel-based inventory Workbook I previously created.
I set-up Cascading Combo boxes with a Brand and a Model combo box, so that when a Brand was selected in the Brand Combo box, from available Brands pulled from the Brands Table, the Model combo box would requery for valid Models based on the Brand selected, from the Hardware Table.
This part worked, except, I couldn't select from those models.
I found that if I went onto another record and came back, the values in the Model box became selectable. This led to also discovering that the values in the Model box would also become selectable if I saved every time I selected a Brand.
This was the Relationships:
And this was the Query used that the Form was bound to (Inventory Query):
SELECT Inventory.Barcode, Hardware.Brand, Inventory.Serial, Inventory.[Model#], Inventory.Current_Assigned, Hardware.Description, Hardware.Type, Inventory.Previous_Assigned, Inventory.BitLocker, Inventory.[Host Name], Inventory.[IO#], Inventory.[Order #], Inventory.Warranty, Inventory.[IP Address], Inventory.Notes, Inventory.Updated, Employee.Legal_Hold, SL.Status, SL.Location
FROM Employee INNER JOIN (SL INNER JOIN (Hardware INNER JOIN Inventory ON Hardware.[Model] = Inventory.[Model#]) ON SL.[ID] = Inventory.[LocID]) ON Employee.[Employee] = Inventory.[Current_Assigned];
---------------------------------
I had someone give me recommendations to change each of the Relationships with Inventory to Join Type 3 (Include ALL record from 'Inventory' and only those records...).
I created a new query doing this, which resolved an issue I didn't know I had. Evidently, my previous query was only showing me a small portion of my records as it was removing any record that had certain fields empty.
I then created a form based on this query and once again configured Brand and Model as combo boxes, except now, I can't select values from either combo box, at all.
Here is the updated relationship table (which also shows the 'Phones' Table that was previously hidden for some reason):
Here is the new Query:
SELECT Inventory.Barcode, Hardware.Brand, Inventory.Serial, Inventory.[Model#], Inventory.Current_Assigned, Hardware.Description, Hardware.Type, Inventory.Previous_Assigned, Inventory.BitLocker, Inventory.[Host Name], Inventory.[IO#], Inventory.[Order #], Inventory.Warranty, Inventory.[IP Address], Inventory.Notes, Inventory.Updated, SL.Status, SL.Location
FROM SL RIGHT JOIN (Hardware RIGHT JOIN Inventory ON Hardware.Model = Inventory.[Model#]) ON SL.ID = Inventory.LocID;
Also, if it helps:
Brand's combo box Row Source:
SELECT Brands.Brand
FROM Brands;
Model#'s combo box Row Source:
SELECT Hardware.Model, Hardware.Brand
FROM Hardware RIGHT JOIN Inventory ON Hardware.Model = Inventory.[Model#]
WHERE (((Hardware.Brand)=[Forms]![QryInv]![Brandbox]));
Jul 21 2021 04:56 PM
I see nobody has replied despite almost 30 views. Perhaps like myself, other members are finding it difficult to work out exactly what issues you are having.
I believe that you now understand the differences between inner and outer joins. However you may find this article useful to clarify your knowledge: Query Join Types
In addition, make sure you understand about Relationships and Referential Integrity
In case it helps, I have an example app which uses 5 cascading combo boxes to filter UK postcodes.
I hope that helps