Subform Based on Look up

[PMal]

Hello,

Hoping this is a simple one, I've tried to search similar post but found nothing....

I have 2 tables, an equipment list, and an events table. the tables are joined by the serial number entered on the equipment list (the serial number on the events table is a look up based on the equipment table). I've created a form for the equipment list and I want to add a subform that lists all the events for said equipment, so a query based on the serial number but I'm scratching my head. I think my issue is I'm trying to filter a lookup value but I can't seem to find a way around.

Any help will be greatly received. Hopefully I've explained it well enough but I can provide more information where needed.

Thanks.

[Welshgasman]

@Pmai
I do not know what you did to create this thread, but there is no text?
Clicking on your name gives me this 'Hello, Hoping this is a simple one, I've tried to search similar post but found nothing.... I have 2 tables, an equipment list, and an events...' but if I tried the +more option, I get back to en empty message.

So please add your question again.

[PMal]

How strange, I tried to add a picture as an edit so something must have gone wrong after this (probably me).

I have 2 tables, an equipment list and an event list. The tables are joined by the serial number of the equipment. For the Event table, this is a lookup based on the column in the Equipment table. I have created a form for the equipment table and I want to add a subquery report to the bottom of the form to show all the events that are related to that piece of equipment. I'm presuming my issue is because the event side of the query will be based on an ID whilst the Equipment is the Serial Number but I cant seem to find a way to navigate it?

Hopefully this makes sense.

[Welshgasman]

Sounds like your structure is incorrect?
I would have
tblEquipment
tblEvents
tblEquipmentEvents

The latter table is a junction table that links the euipment with any events. I would still use the autonumber ID for both the first two tables. These then get stored in that junction table.

HTH

[CJ_London]

Your design does not sound right - unless an event can only use one item of equipment.

Suggest show your table design for each table plus relationships and provide a brief explanation of what an event is

To add a picture, click on the insert image button - 4th in from the right on the basic menu

What I would expect to see as an absolute minimum is something like

tblEquipment
EquipmentPK
SerialNum
EquipmentDesc

tblEvents
EventPK
EventName
EquipmentFK - links to EquipmentPK
DateFrom
DateTo

[PMal]

OK it would appear that my method might be way off, full disclosure, I haven't touched Access in roughly 8 years but after starting a new job, I thought I'd try my hand again, it would seem I'm rusty to say the least.

The event table is to be used to attached things like maintenance, audits, repairs etc to equipment so I can keep a history of what happens. I've attached the picture of the current set up.

[Welshgasman]

Again, I would have thought audit, maintenance etc would be separate tables?

Also do not use table lookups, do them yourself on the forms.

[PMal]

Again, I would have thought audit, maintenance etc would be separate tables?

Also do not use table lookups, do them yourself on the forms.

That seems to have fixed it, taking out the look out from the table. Thank you.

In regards to the separate table for each event type, it is supposed to just be a snapshot of the history so I don't see a need from my view point, unless there's some reason is Access that I'm not aware of?

[mike60smart]

Hi

Your Relationships between tblEquipment_Inventory and tblEvents is wrong

tblEquipment_Inventory currently has a Primary Key of "ID" - this should be "EquipmentInventoryID"

Then in tblEvents replace "ID" with "EventID"
Then add a Foreign Key named "EquipmentInventoryID"

Link the PK from tblEquipmentInventory to the FK in tblEvents

[PMal]

Hi

Your Relationships between tblEquipment_Inventory and tblEvents is wrong

tblEquipment_Inventory currently has a Primary Key of "ID" - this should be "EquipmentInventoryID"

Then in tblEvents replace "ID" with "EventID"
Then add a Foreign Key named "EquipmentInventoryID"

Link the PK from tblEquipmentInventory to the FK in tblEvents

Hi, I don't think I'm clever enough to do that, my SQL skills are basic to non-existent. With the for sub-query now working as I want it to by joining the serial numbers, will it cause me issues down the line?

[ArviLaanemets]

In regards to the separate table for each event type, i...

Not a separate table for every event type! You need an Events table, where all those event types are defined (EventID, EventName, with values like 1, "Meintenance"; 2, "Audit"; 3, "Repair", etc.), and an ItemEvents table, where is registered, when and for which item an event occurrred, and who did it.

Edit:
With such design, as bonus, you get a full history of all events for item. At any time you can see, what and when happened something with this specific item.

And having all events registered in ItemEvents table, you can clear all event info from Items table - it will be abundant there.

Another info you have to consider to remove from Items table is location. Have a Locations table where all possible locations for all items are registered, and a ItemLocations table, which will actually be a Item Location Log , where is registered all location changes for every item, along with change date. An advice - add some 'fake' locations, like "Scrapped", "InRepair", "NotInUse", etc. so you can register those movements too!

[mike60smart]

Hi

You should always link Related Tables using a PK and FK

[pbaldy]

First post was moderated, probably after trying to add picture. I'm not getting notifications of moderated posts, so I just saw this now. Sorry for the delay.

[Welshgasman]

First post was moderated, probably after trying to add picture. I'm not getting notifications of moderated posts, so I just saw this now. Sorry for the delay.

Paul, would you be using a Gmail address on here?

[pbaldy]

Paul, would you be using a Gmail address on here?

Yes, Matrix said that may be the problem, though I recall somebody testing a different provider and it still not working? Not sure, old brain.