Option Group vs. Union Queries

[Alex2015]

Apparently, I've hit a 255 character limit with my previous union queries. So my next thought was to break up the union queries into smaller chunks & have an option group invoke a specific query which is also filtered by the EmpID field on the same form.

Here's what I mean: the "frmViewAnEmployee" attachment shows just one of the forms that I'm trying to do this on. Originally, the "Run Report" command button was supposed to execute "qrySingleReport (a union query)" according to which employee you chose in the combo box (combo box is tied to the EmpID). This worked perfectly until I tried to expand the union query even further.

So I divided the union query into smaller chunks & added an option group to the form. My thought is this: user selects the employee from the combo box, the EmpID field is automatically filled in by this, the user then clicks on the option that designates which work area (unit) the employee is from, & then clicks the command button to get the filtered results. Is this possible without VBA? If not, how do I go about it? Thank you.

 

[plog]

Why is this a UNION query? All of your SELECT, FROM and INNER JOIN clauses are the exact same. The only differences are the WHERE clauses. You could combine your WHERE clause logic.

However, the correct thing to do is to make a table to hold all valid tblUnits.Unit and tblJunction.Item permutations and then INNER JOIN that table on the appropriate fields.

 

[gemma-the-husky]

are you sure there is a 255 character limit

anyway, another way of phrasing a union query is to have a number of subqueries, and then simply

select * from queryA union
select * from queryB union
select * from queryC

which would not get to 255 characters.

 

[Alex2015]

Why is this a UNION query? All of your SELECT, FROM and INNER JOIN clauses are the exact same. The only differences are the WHERE clauses. You could combine your WHERE clause logic.

However, the correct thing to do is to make a table to hold all valid tblUnits.Unit and tblJunction.Item permutations and then INNER JOIN that table on the appropriate fields.

To be honest, I was following this guy's example:
https://www.youtube.com/watch?v=-tUMfbsJVTA&list=PL3E83E94F90BA0F44&index=8

I'll have to do some tinkering to see if I can get the multi-value tables to work.

 

[Alex2015]

are you sure there is a 255 character limit

anyway, another way of phrasing a union query is to have a number of subqueries, and then simply

select * from queryA union
select * from queryB union
select * from queryC

which would not get to 255 characters.

I tried overwrite my previous union query with the updated, longer union query & the attached file is the error message I got.

I'll have to look up how exactly my subqueries would look like in code. I don't know if I should go this route or the multi-value table route, but this seems more up my alley.

 

[plog]

Seriously, a UNION query isn't the way to go. Work smarter not harder. Combine the logic of the WHERE clauses, don't combine all those queries.

If you wanted to pull all Adams, Brads and Chucks from a Contact table, you wouldn't UNION 3 queries together (1 for each name), you would compile a WHERE clause to accomplish that in one query:

WHERE FirstName='Adam' OR FirstName='Brad' OR FirstName='Chuck'

You are in the same situatuion. Combine your WHERE clauses, don't use UNION.

 

[arnelgp]

i think you dont need a union query there.
all you have to do is change your last criteria (i cant read it), something to:
tblUnits.Unit = "5th Floor Med Surg" AND (unreadable.Item In ("ACLS","BLS","Contingency Plans", etc.))

 

[Alex2015]

Seriously, a UNION query isn't the way to go. Work smarter not harder. Combine the logic of the WHERE clauses, don't combine all those queries.

If you wanted to pull all Adams, Brads and Chucks from a Contact table, you wouldn't UNION 3 queries together (1 for each name), you would compile a WHERE clause to accomplish that in one query:

WHERE FirstName='Adam' OR FirstName='Brad' OR FirstName='Chuck'

You are in the same situatuion. Combine your WHERE clauses, don't use UNION.

Thanks Plog. I wanted to update this thread so that you guys don't think I just bailed. I'm going to try something tomorrow. It totally makes sense what you're saying. Hopefully I can get somewhere by combining the WHERE clauses.

 

[Alex2015]

i think you dont need a union query there.
all you have to do is change your last criteria (i cant read it), something to:
tblUnits.Unit = "5th Floor Med Surg" AND (unreadable.Item In ("ACLS","BLS","Contingency Plans", etc.))

I'll take this into consideration when I try something tomorrow. I'll let you know how it turns out.

 

[Alex2015]

Ok guys, I finally got some time to attempt this. Attached are two pics for my SQL statements. I didn't know if attaching a Word document is allowed on this site. One attachment is the long version & the other is the shorter version. I'm pretty new at writing SQL, hence the reason why I submitted two versions. Please advise if I'm doing it correctly.

Assuming I'm on the right track, I have some points to make:
-The items highlighted in green have their due dates calculated every two years, while...
-The items highlighted in yellow are calculated every year
-This conflicts with the DateAdd function highlighted in blue

So, how do I fix the fact that I have different items with different due dates? Should there be a WHERE clause included in the DateAdd statement that addresses this? Thank you in advance.

 

[CJ_London]

your short version is nearer the mark modify to

...... AND tblJunction.Item IN ("ACLS",BLS", ....)

 

[CJ_London]

This conflicts with the DateAdd function highlighted in blue

modify to

Dateadd("yyyy",iif(tblJunction.Item in ("ACLS","BLS"),2,1),itemcompletiondate)

 

[Alex2015]

modify to

Dateadd("yyyy",iif(tblJunction.Item in ("ACLS","BLS"),2,1),itemcompletiondate)

Hi London,

Thanks a bunch. Attached is my retry. So if I got it correct this time, am I to assume this is how it reads:

-For the DateAdd line, it reads:
Calculate by year, if the item in tblJunctionItem is ACLS or BLS then multiple by 2 years, for everything else in the table multiply by 1 year, display as ItemDueDate.

-For the WHERE clause, it reads:
...include all items IN tblJunction.Item (such as ALCS, BLS, etc.).

Also, I just tried it & it's giving me a syntax error for the SELECT line.

 

[CJ_London]

looks like you are missing a dot here

iif(tblJunction.Item

Also, if posting code, it is much more helpful to responders if you copy and paste the code into the thread - use the advanced option and you can surround with code tags, highlight etc.

Then us poor responders can see what you have much more easily, copy it ourselves etc

 

[plog]

Not a big fan of IN, especially with that many values. I mean, where's this SQL going to run? In a database, right? So why not store all those values in something made for storing values and makes it easy to manage and edit values in it....like a table?

Chances are, all those values in your IN are already in some table (Items table, per chance?). Now its just a matter of adding a new field to that table so that you can easily mark which ones limit this query. That way if this list ever adds, shrinks or some item gets changed all you have to do is go to the table and update there instead of digging into SQL when those changes happen.

Then to use it, you don't use it in an IN in the WHERE clause, you use it in an INNER JOIN clause.

 

[Alex2015]

looks like you are missing a dot here

iif(tblJunction.Item

Also, if posting code, it is much more helpful to responders if you copy and paste the code into the thread - use the advanced option and you can surround with code tags, highlight etc.

Then us poor responders can see what you have much more easily, copy it ourselves etc

Thank you. I will post the code in here from now on.

 

[Alex2015]

Not a big fan of IN, especially with that many values. I mean, where's this SQL going to run? In a database, right? So why not store all those values in something made for storing values and makes it easy to manage and edit values in it....like a table?

Chances are, all those values in your IN are already in some table (Items table, per chance?). Now its just a matter of adding a new field to that table so that you can easily mark which ones limit this query. That way if this list ever adds, shrinks or some item gets changed all you have to do is go to the table and update there instead of digging into SQL when those changes happen.

Then to use it, you don't use it in an IN in the WHERE clause, you use it in an INNER JOIN clause.

You are correct. The items are part of their own table (see attached for relationships). All 22 items are part of tblItems.

The idea is to do this (see the 2nd attachment):
The combo box at the top selects the employee, the form fills in the appropriate fields, the field EmpID is hidden in background but is tied to the SQL code, the user then hits the "Run Report" command button, & all of the info for this specific employee gets displayed in an Excel spreadsheet. Of course, this will get more complicated for the form that involves returning all of the info for an entire work area, but I thought I should get this part functioning properly first.

I'll have to visit your suggestion tomorrow. I'll report when I have something.

 

[Alex2015]

Not a big fan of IN, especially with that many values. I mean, where's this SQL going to run? In a database, right? So why not store all those values in something made for storing values and makes it easy to manage and edit values in it....like a table?

Chances are, all those values in your IN are already in some table (Items table, per chance?). Now its just a matter of adding a new field to that table so that you can easily mark which ones limit this query. That way if this list ever adds, shrinks or some item gets changed all you have to do is go to the table and update there instead of digging into SQL when those changes happen.

Then to use it, you don't use it in an IN in the WHERE clause, you use it in an INNER JOIN clause.

Right now, the structure of tblItems is this:

PK: Item (field names are ACLS, BLS, etc.)

Are you saying that I should create separate fields for the same items? Such as:

PK : Item (5th Floor Med Surg ACLS, 5th Floor Med Surg BLS, 6th Floor Med Surg ACLS, 6th Floor Med Surg BLS, etc.)?

 

[plog]

Right now, the structure of tblItems is this:

PK: Item (field names are ACLS, BLS, etc.)

You lost me. How many fields does tblItems have? You shouldn't be using values as field names (ACLS, BLS, etc).


To implement your criteria that currently exists in your WHERE clauses, you need a table that lists all the Unit/Item permutations. You would need a table that has this:

Unit, Item
5th Floor Med Surg, ACLS
5th Floor Med Surg, BLS
5th Floor Med Surg, Chest Tubes
etc.

That may even be logically reduceable based on your data. Perhaps the Unit isn't necessary if you care about all specified Item data regardless of Unit.

Then instead of applying your criteria explicitly in the WHERE clause, it would work implicitly by putting it in the INNER JOIN.

 

[Alex2015]

You lost me. How many fields does tblItems have? You shouldn't be using values as field names (ACLS, BLS, etc).


To implement your criteria that currently exists in your WHERE clauses, you need a table that lists all the Unit/Item permutations. You would need a table that has this:

Unit, Item
5th Floor Med Surg, ACLS
5th Floor Med Surg, BLS
5th Floor Med Surg, Chest Tubes
etc.

That may even be logically reduceable based on your data. Perhaps the Unit isn't necessary if you care about all specified Item data regardless of Unit.

Then instead of applying your criteria explicitly in the WHERE clause, it would work implicitly by putting it in the INNER JOIN.

Sorry; I may have used the incorrect terminology but I think we're almost on the same page. The "Before" attachment is what my table (tblItems) has been looking like. The employee is assigned a work area (5th, 6th Floor, etc. which are listed in tblUnits) & the queries were meant to be primarily based off of said work areas.

However, the items in tblItems are all generic names that each work area calls their training modules. It gets complicated because those generic items all have different due dates according to the work area in question. For example, Chest Tubes may be done yearly on the 5th Floor & every two years on the 6th Floor.

The "After" attachment is what I came up with for tblItems. Not sure if you mean to separate the units & items in this pic or just leave them in the same cell. I really hope I'm making some sense here & progress for that matter :banghead: