IF ID Is not Null show TYPE from other table

[superjeff]

Hello Forum of Genius,

I would like to show choices in a Combo Box only if there is information in any of three different tables. So, if I was writing a sentence it would say something like this.
SELECT[SOLDIER_TBL].[EVENT_TYPDTE_ID] IF [EVENT_TBL].[EVENT_ID] Is Not Null AND IF [FM_ID].[EVENT_ID] Is Not Null AND IF [LODGING_ID] .[EVENT_ID] Is Not Null

This way if a service member attended an event (EVENT_ID) or if we captured any data in the past from an event FM_ID and LODGING_ID then only those EVENTTYPDTE_IDs would appear in the combo box because eventually I will have a bunch of events and do not want the users to have to weed through the ones of which the soldier never attended.

I greatly appreciate your time and selfless support.


Respectfully,
Jeff

[John_G]

Hi -

I have a strong suspicion that your data structure may not be properly normalized - Can you post your tables and the relationships between them? From what I can tell, you have a many-to-many relationship between service members and events, which means you need another table (e.g. Members-Events) to link the two.

John

[superjeff]

John,
I have a strong suspicion you are probably correct. I have been tasked to rebuild a database with very little Access experience but with much tenacity. Please root for tenacity. The event table holds Event Location, Date, Type, etc.. Soldier table consist of Name, Rank, Unit, etc. The Lodging table contains date checked-in, mileage, etc.. The Family table holds family member info. All tables contain the Service members SSN as well as an Event Code. The only relationship created is I have created 3 sub-forms the Event, Lodging and Family that link to the parent Soldier. That relationship keeps the info synced for each service member and each event. I am a hopeless train wreck?

[John_G]

Hi -

You do have some information where is is not needed, and you are missing a table that will make the whole exercise easier.

The eventual solution may seem a bit complicated, because a soldier can have multiple family members (more on that in a bit)

SSN - Should not be in the Event table, nor should it be in the Lodging table
Event Code - Should not be in Soldier, Family or Lodging tables.

Now, a soldier can attend an event, stay in some lodging with zero or more family members - I'll call this a Visit.

The bold text identifies tables we already have; we now need new one to tie them all together - call it Visit.

Visit might have:

Visit_ID - PK to this table
SSN - FK to Soldier
Event_ID - FK To Event
Lodging_ID - FK to Lodging

Because there can be more than one family members on these visits, we need another new table - Family_Visit, which could have:

Family_Visit_ID - PK to this table (the purists would say is required, but it's not vital)
Visit_ID - FK to Visit
Family_ID - FK to Family

It's not clear from your description of how the lodging works, but you might be able to put the lodging data into Visit table.

I have made the assumption here that for any given visit, everyone stays in the same lodging for the same amount of time, and it is a good starting point.

With no disrespect intended - you have made the common mistake that a lot of people make, which is to think about how your forms work before you have the data structure properly sorted out. Having the structure right first can save you from endless headaches later on.

HTH

John

[superjeff]

John, Master of all things Patient,

I really appreciate your time. I have followed your sage advice and I am much better off. I do hope that you could shed some light on the form part. I have the main form derived from the VISIT table. Sub-form 1 = FM_VISIT (Sub-form of FM_VISIT is FAMILY_TBL), Sub-form 2 on VISIT = EVENT, Sub-form 3= LODGING, Sub-form 4 = FAMILY.
When I search for SSN the forms populate with the correct data. Yay! BUT.. I over-write the original event data... Any ideas on how to not over-write the original but still be able to use all the data already collected that hasn't changed, like DOB.
Thank you again,
Jeff

[John_G]

Hi -

Can you clarify what the database is tracking, and what its purpose is? When you query the database, what is the information you are trying to get? I ask this because the answers to these questions help to determine how the data and the interfaces to it are organized This is true for ANY database - there is no point in having a database if you can't really say what it is the database is supposed to do. (It's also a common problem for those of us asked to design databases).

For the question in the post above - when you search for visits using SSN, and the see the data in the subforms, you don't want to be able to change any of the subform data, or not much of it anyway. When you enter a new "visit", the only new data that is related to the visit would be lodging. The other data (soldier, event, family) does not change; a "Visit" only refers to it, using SSN, Event_ID etc. So, when you say you "overwrite the original data" - what data is being overwritten?

Your application may be one where the data entry forms, and the data display (query) forms, are completely different.

John