Help with relationships (1 Viewer)

Hi All, thanks in advance

I maintain military equipment with inspections coming up requiring printed forms.

tblPersonnel
tblEquipment
tblComponents
tblInspections
tblCatalog

One person has many equipment. Each one (tblequipment)equipment has many (tblInspections)
has many (tblComponents)
tblCatalog is simply a reference pick list, that holds part numbers to describe the components and equipment (price, location, ect)

What is getting me is trying to pass information through subforms

PersonID 1
has equipments ID 18, 19, 20
each has inspections

it's passing personID beautifully through all forms/relationships, but I'm having to manually enter the EquipmentID, for components and inspections, which is a bummer and besides the point of a database.
Any Ideas? Missing keys, relationships?
https://goo.gl/photos/RtWK8yz8ufR38Ahr7
https://goo.gl/photos/32zGxUi3ojsrso7a9

Rather than providing a picture of a sample of data, let us see the relationships between the tables. This will probably show the issue. For a start, I'd expect there to be another table linking tblPersonnel and tblEquipment.

Looking at your relationship screenshot, you do have improper relationships among your tables. Specifically TCATALOG, TCOMPONENTS and TEQUIPMENT: Their relationship is essentially a loop. That's wrong.

You should only be able to trace one path between any two tables. Your relationship allows 2 ways (directly and then indirectly via the 3rd table). That needs to be fixed. I don't understand your data well enough to know what the fix is, but one of those relationships needs to go.

I also see that TINSPECTIONS has an IDPERSONNEL field in it. Does that link to the TPERSONNEL table? If so, that is incorrect as well for the same reason.

I agree on the tCatalog, but T Catalog is the main list to describe the items in Benchstock, Orders, Equipment, Components. Any ideas other than making and maintaining identical matching TCatalog tables for each list?

Thanks on the IDPersonnel, I did see that and corrected it.

I solved my original problem by using a form tied to each piece of equipment, and a subform each for the inspections and components. I do wish there was a way to be able to update each person info, equipment, inspections from the same main form. Such as a few double clicks in subforms adding todays date for each piece of equipment (there's only three) and Done!

Get your tables and relationships designed to meet your requirements. That means you have to have a clear set of requirements. It also means you can model your tables and relationships, and you can create some test data and a few scenarios identifying some business process with inputs and expected results. Run those scenarios against your model -does it provide the proper data.
You can do this with pencil and paper. Do this and get your model working before jumping into Access and Forms etc. You will save yourself a lot of pain and frustration.

See this related post.

Good luck

I did =) after seeing the replies from the forum and it did help.

tBenchstock
tOrders
tComponents
tEquipment

currently I have
queries combining
Benchstock&Catalog
Orders&Catalog
Components&Catalog
Equipment&Catalog

So far, it is working alright, could it cause issues later?

Need to pull data from the Same list out of tCatalog. They are the same components each, Catalog carries the price, location, UI. How to accomplish that without a loop, or making it a table that is not connected and simply used as a picklist? Sorry, my computer is refusing to attach the Db. If I can later, I will!
I'm just starting out, Thanks a ton!

Readers are at a disadvantage.
What are these and where do they fit in your "business"?

Benchstock
, Orders
, Equipment
, Components.

Can you describe TCatalog -what it is, its purpose etc?

A 1-2 line description of each Table (and field) will help you with design, will help readers with communication, and will help anyone using/maintaining/providing training for you database.

Also, a short description of the "Business" in simple terms would help put your post into context.

I am attaching a draft model based on parts of your post.

Good luck