Option Group vs. Union Queries

[plog]

You should store discrete pieces of data by itself. That means you should use 2 fields for the AFter data.

 

[Alex2015]

You should store discrete pieces of data by itself. That means you should use 2 fields for the AFter data.

It's been a few days; I hope you're still around. I get what you're saying about the AFTER DATA as far as normalization goes, but I ran into conflicts because the table has multiple instances of the same items.

Originally I had it like:

ex.
Item
5th Floor Med Surg - ACLS

Item
6th Floor Med Surg - ACLS

At first, my solution was to create the item name for the 1st field & the work area for the 2nd field:

ex.
Item
ACLS

WorkArea
5th Floor Med Surg

Item
ACLS

WorkArea
6th Floor Med Surg

However, because the same item is used in multiple work areas, you can't use the same names for them because it creates conflicts. So, my solution was to do this:

ex.
Item
ACLS (5)

WorkArea
5th Floor Med Surg

Item
ACLS (6)

WorkArea
6th Floor Med Surg

Not sure if this is the best method or if I should go back to my original way.

 

[plog]

You need to get all valid Item/WorkArea permutations into a record together.

WorkAreaItems
WorkArea, Item
5th Floor Med Surg, ACLS
6th Floor Med Surg, ACLS
5th Floor Med Surg, BLS

 

[Alex2015]

You need to get all valid Item/WorkArea permutations into a record together.

WorkAreaItems
WorkArea, Item
5th Floor Med Surg, ACLS
6th Floor Med Surg, ACLS
5th Floor Med Surg, BLS

I did what you suggested & created a table (tblItems) with all the possible permutations for the training items. So here's my code so far:
SELECT tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.MiddleInitial, tblEmployees.Title, DateAdd("yyyy",2,[LicenseRenewed]) AS LicenseDueDate, tblEmployees.[Active?], tblUnits.Unit, tblJunction.Item, tblJunction.ItemCompletionDate, DateAdd("yyyy",2,[ItemCompletionDate]) AS ItemDueDate

FROM tblUnits INNER JOIN (tblItems INNER JOIN (tblEmployees INNER JOIN tblJunction ON tblEmployees.EmpID = tblJunction.EmpID) ON tblItems.Item = tblJunction.Item) ON tblUnits.UnitID = tblJunction.Unit

WHERE (((tblUnits.Unit)="6th Floor Med Surg") AND ((tblJunction.Item)="6th Floor Med Surg - ACLS")) OR (((tblJunction.Item)="6th Floor Med Surg - BLS"));

I know you said to separate discrete data for the items but I had a heck of a time trying to make that work without messing up my forms as well. I had to resort to the following nomenclature: unit name - item name1, unit name - item name2, etc.

The above code works great for the two items that are due every two years. How do I fit the items into the above code that are due annually? I want to write the line in the INNER JOIN section as you suggested but I'm just confused about how to incorporate all of the annual items with what I have above.

 

[plog]

You will no longer have a WHERE clause in your query for Unit/Item permutations.

You will have a datasource that has all the ones you want to report on. This is why you need a query that gets all the permutations together so you can use it.

So instead of this:

WHERE (((tblUnits.Unit)="6th Floor Med Surg") AND ((tblJunction.Item)="ACLS")) OR (((tblJunction.Item)="BLS"));

You would need a datasource like so:

ReportUnit, ReportItem
6th Floor Med Surg, ACLS
6th Floor Med Surg, BLS

Let's call that datasource 'ReportingUnitItems. Then, instead of the WHERE you would have another INNER JOIN from your existing datasources to ReportingUnitItems.

 

[Alex2015]

You will no longer have a WHERE clause in your query for Unit/Item permutations.

You will have a datasource that has all the ones you want to report on. This is why you need a query that gets all the permutations together so you can use it.

So instead of this:

WHERE (((tblUnits.Unit)="6th Floor Med Surg") AND ((tblJunction.Item)="ACLS")) OR (((tblJunction.Item)="BLS"));

You would need a datasource like so:

ReportUnit, ReportItem
6th Floor Med Surg, ACLS
6th Floor Med Surg, BLS

Let's call that datasource 'ReportingUnitItems. Then, instead of the WHERE you would have another INNER JOIN from your existing datasources to ReportingUnitItems.

Sorry; I was editing my previous response. I'm going to give this a shot. Would it help to upload a stripped down version of my database once I'm done?

 

[plog]

Yes please. Actually if you could upload now--I just need the source data.

 

[Alex2015]

Yes please. Actually if you could upload now--I just need the source data.

Thanks bud. I'm at work right now but once I'm done here, I'll upload in a bit.

 

[Alex2015]

Yes please. Actually if you could upload now--I just need the source data.

Attached. I left tblItems the way it is but it doesn't have to remain that way if it's going to cause issues.

 

[plog]

You don't have a table that lists all Item/Unit permutations you want to report on. You still have the improperly structured tblItems. It should have 2 fields in it, not 1 which holds the data of 2 fields.

 

[Alex2015]

You don't have a table that lists all Item/Unit permutations you want to report on. You still have the improperly structured tblItems. It should have 2 fields in it, not 1 which holds the data of 2 fields.

Right; but when I tried to create separate fields for the work areas & the items, I got an error message:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.

It didn't like it when I did:
WorkArea
5th Floor Med Surg
6th Floor Med Surg

Item
ACLS
ACLS

I don't know how to get around that.

 

[plog]

Then remove the primary key. If you feel you need one add an autonumber and make that the primarky key. Or make it a composite key using both fields.

 

[Alex2015]

Then remove the primary key. If you feel you need one add an autonumber and make that the primarky key. Or make it a composite key using both fields.

Fixed. I ended up making the primary key an autonumber & linking it to the junction table via tblJunction.Item. This will end up messing with the data entry forms but I'll worry about that later. I want to see how this is going to work. Thanks.

 

[plog]

So are you good then?

 

[Alex2015]

So are you good then?

Lol; not quite. My original posting had to do with queries. Your suggestion was awesome & I did incorporate the table of permutations into my database. Looking good so far.

I wrote a very basic query:

SELECT tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.MiddleInitial, tblItems.WorkArea, tblItems.Item, tblJunction.ItemCompletionDate
FROM tblItems INNER JOIN (tblEmployees INNER JOIN tblJunction ON tblEmployees.EmpID = tblJunction.EmpID) ON tblItems.ItemID = tblJunction.Item;

How would the code look to add a mixture of annual & bi-annual items to the INNER JOIN so that the due dates are properly calculated? For example: Chest Tubes (due every year) & ACLS (due every two years).

I have a gut feeling that I should have separate tables for the frequencies (such as once, annually, & every two years). That might be the problem.

 

[plog]

Is your data set up to determine that? I mean, do you have a datasource of items and their schedules?

Also, what do you expect in terms of results? Do you want a query that list each item with the next date something is due? Or do you want a list of everything due in the next 5 years?

 

[Alex2015]

Is your data set up to determine that? I mean, do you have a datasource of items and their schedules?

Also, what do you expect in terms of results? Do you want a query that list each item with the next date something is due? Or do you want a list of everything due in the next 5 years?

I just edited my previous posting. I'm starting to think that maybe I need separate tables for all of the frequencies so that I can easily create queries & calculate the due dates for the items depending on which frequency table they are located in.

Right now, all of the "completion dates" for the training modules are located in one table (so every once, annual, & bi-annual item is thrown in there). That makes it extremely difficult to write the DateAdd line for the query because it's not just one calculation that I have to make.

What I wanted with the results was to automatically calculate the due date one year into the future for one item & two years into the future for a different item. It gets more complicated because the same item from WorkAreaA may be due every year, but in WorkAreaB, it may be due every two years.

 

[plog]

It gets more complicated because the same item from WorkAreaA may be due every year, but in WorkAreaB, it may be due every two years

You don't need seperate tables for this, you just need to store it in the proper place. From the above sentence, that proper place is in the table we worked on with Unit/Item.

Also, for this new field you need to use the lowest common unit of length of time something can be due. If the most frequent thing is tested every other month, then every item must have their frequency expressed in months.

 

[Alex2015]

You don't need seperate tables for this, you just need to store it in the proper place. From the above sentence, that proper place is in the table we worked on with Unit/Item.

Also, for this new field you need to use the lowest common unit of length of time something can be due. If the most frequent thing is tested every other month, then every item must have their frequency expressed in months.

Thanks Plog. I've been meaning to thank you for sticking through to the end. Your help has been immensely valuable.