EAV Initial Model (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 03:34
Joined
Dec 5, 2017
Messages
843
Hi All -

I've attached my rough draft of where I believe this is going. I'm beginning to work on figuring out how to get data into and out of it. It's a struggle - especially the data entry aspect.

The most telling thing to look at I believe is the relationships window.

I have two junction tables in line with each other which has me scratching my head a bit.

Basically - -Jobs (specifically JobNumber/Task/Resource) drives and houses all details about a job. Inspection events will be the actual going to the floor and making an inspection- - - which can be of ANY type and may have any number of pieces of data recorded. This is reflected in the relationship of tblInspectionTypes, tblItems, tblInspectionTypeItems.

tblInspectionTypeItems is the many-to-many table that matches inspection types to various data items to be recorded.

Opening qryItemsByInspectionType will show exactly how that all matches up.

So far what I have just described functions like a multi-table lookup. I understand fairly well how to work with this specific area.

But the actual inspections are a function of tblInspectionTypeItems, tblInspectionEvents, and the junction table that joins them- tblInspectionEventDetails.

tblInspectionEvent records general inspection info (e.g, date, inspected by whom, machine operator, job number, etc.)

tblInspectionEventDetails is where I would lookup tblInspectionTypeItems to find the combination of type and item that is to have data collected on (e.g., assembly/length versus mill/length) and then pass or fail whatever type/item combination I have chosen to inspect.

So I have lookups based on lookups and I am using numeric IDs and FKs for all of them.

I have a ton of questions but the primary one right now is if I have a form based on tblInspectionEvent what is a constructive approach for having a way to choose type of inspection on that main form and somehow filter only the records in tblInspectionTypeItems that reflect the choice of type.

Basically from tblInspectionEvent, I want
1) a cbo to choose which type of inspection
2) record the numeric ID rather than the string
3) have that choice apply a filter that returns a datasheet subform that shows me all of the related records in tblInspectionTypeItems but only record the numeric ID values of the ones I choose to inspect.

I obviously haven't worked this out yet. It's starting to make my head spin a bit - - which is why I am now asking for your feedback before I dig myself a really deep hole!

Thanks as always!

Tim
 

Attachments

  • QCDB - EAV Model.zip
    100.5 KB · Views: 184

mike60smart

Registered User.
Local time
Today, 07:34
Joined
Aug 6, 2017
Messages
1,899
Hi Tim

I took a quick look at your tables and your explanation indicates that you need Cascading Combobox's.

If you can fill in the InspectionTypeID's into the attached Excel sheet I will give you an example.

View attachment Items.zip
 

Zydeceltico

Registered User.
Local time
Today, 03:34
Joined
Dec 5, 2017
Messages
843
Hi Tim

I took a quick look at your tables and your explanation indicates that you need Cascading Combobox's.

If you can fill in the InspectionTypeID's into the attached Excel sheet I will give you an example.

View attachment 75414

Hi Mike,

I am really grateful for your offer and I’ll take you up on it but it will be a few days. Our IT department took my laptop for the weekend to update and upgrade over the weekend.

Thanks!

I saw your PM. I’ll respond to that also.
 

Zydeceltico

Registered User.
Local time
Today, 03:34
Joined
Dec 5, 2017
Messages
843
Hi Tim

I took a quick look at your tables and your explanation indicates that you need Cascading Combobox's.

If you can fill in the InspectionTypeID's into the attached Excel sheet I will give you an example.

View attachment 75414

Hi Mike -

I took a look at your Excel spreadsheet.

My table tblInspectionTypeItems is exactly what you are looking for. It is already in the db - unless of course I am misunderstanding you. Still don't have my laptop back from IT. Should be Monday.
 

mike60smart

Registered User.
Local time
Today, 07:34
Joined
Aug 6, 2017
Messages
1,899
Hi Tim

If you want to be able to select an Inspection Type and then Only see those Items Associated with the Type selected then you must first have a table structure like the attached example.

types.JPG
 

Users who are viewing this thread

Top Bottom