Multiple Date Fields based on Criteria on the same Report Row

[Smokeeater]

Bear with me as I try to explain my situation. I have a huge table - don’t ask – I inherited it, and yes, we are making headway).

Along with the standard name, and employee ID #, this table contains multiple fields to track license expirations, based on the type of equipment. To keep it simple, they are [Equip1License], [Equip2License], [Equip3License], etc., and for this part of my project, there are 9 total License fields I am working with.

Additionally, each of the different types of equipment has an [active] field tied to. This is a checkbox next to each date field. Essentially, not all employees are licensed for each of the 9 different pieces of equipment, and for the equipment they are licensed for, their current status on that equipment is either “active” or if unchecked, then “non-active”.

For example:

[Equip1License], [Active]
[Equip2License], [Active]
[Equip3License], [Non-Active]
[Equip2License], [Active]
[Equip3License], [Non-Active]

Every 2 years, the employee must recertify on the equipment for which they are licensed on. On the form that is used to show the status for each employee, we use conditional formatting to visually show the status of the License dates in terms of how close they are to expiring. Anything before today’s date will be red, meaning that technically their license had expired, and anything from today’s date looking forward +60 days, is shown in yellow, as an indicator that the expiration is coming due soon. That works fine.

I have individual reports for each piece of equipment, with the same conditional formatting on the license dates, and again, those work fine.

Finally…..here is where I am struggling. I have need for an overall license report for all 9 license fields, on the same report. I have set up induvial queries, that filter by active (-1), and then set the license date field criteria to “<Date()+60”. This pulls back all records of those employees who are active, and who’s license is either already expired or will be in the next 60 days. Again, each query works fine.

What I am doing with each of the 9 queries is appending them to a table, that has a field for employee name, employee # number, and then a field for each of the (9) license dates (one for each append query). You can see where this is going – if an employee has more than one license that is meets the criteria, then it creates a different record for each type of equipment. And when I carry this through to the report, the same issue applies.

[Employee Name]	[Equipment1]	[Equipment2]	[Equipment3]	[Equipment4]
Jones, Bill	02/01/2020
Jones, Bill		01/19/2020
Jones, Bill				02/22/2020

What I would like is for everything to be on the same row as this:

[Employee Name]	[Equipment1]	[Equipment2]	[Equipment3]	[Equipment4]
Jones, Bill	02/01/2020	01/19/2020		02/22/2020
Smith, John				03/03/20
Taylor, James	12/15/19		02/20/2020

I have tried using the group function in both the query, and on the report, but is still “stacks” the dates.

Any ideas would be greatly appreciated. I know I am close, but just missing that last piece of the puzzle.

Thanks.

 

[namliam]

[Equip1License], [Equip2License], [Equip3License]
This usually is a sure sign of a design issue or two hidden in the database, a better design would be a table such
EquipmentNumber LicenceDate

And store each licence as a seperate record, your current design is tailored to the way you want to see it i.e. in spreadsheet view which is causing your headaches here.

Also using spaces in column names again sure sign of more headaches, while spaces and other special characters are allowed. It is best practice to not use them.

What I am doing with each of the 9 queries is appending them to a tabl

Worse yet, storing stuff to tables you dont want to store. Your solution with a proper design would be easy, though here too it is relatively easy unless you go for the ultimate solution of doing a proper re-design.

Easiest way around this is to use your flawed design to work for you for a change... Below is to give you the basic idea to run one simple query instead of all this overhead. Offcourse you will need to tweak and adjust it.

Select [Employee Name]
      , IIF(equipment1 <Date()+60 ; equipment1; null) Equipment1
      , IIF(equipment2 <Date()+60 ; equipment1; null) Equipment2
      , IIF(equipment3 <Date()+60 ; equipment1; null) Equipment3
      , IIF(equipment4 <Date()+60 ; equipment1; null) Equipment4
From   Yourtable
where
    equipment1 < Date() + 60
or  equipment2 < Date() + 60 
or  equipment3 < Date() + 60 
or  equipment4 < Date() + 60

 

[Smokeeater]

Mailman - that you for the replace, and yes, to confirm, I have no spaces in any of my fields. I just used that for my example. I will work on designing something along the lines of code you provided. I can see already how that will return the results I am looking for.

Thank you!

 

[Pat Hartman]

Since you are modifying the app, now would be a good time to take an extra day or two and actually fix the underlying design flaw. Everything you need to do going forward will be simplified. Granted, you may have to change a few forms, reports, and queries to get there but once they're changed you can move on with your life with a weight lifted. Going forward, you won't need to modify anything if you need to add a new license or modify an existing one.

 

[Smokeeater]

[Equip1License], [Equip2License], [Equip3License]
This usually is a sure sign of a design issue or two hidden in the database, a better design would be a table such
EquipmentNumber LicenceDate

And store each license as a separate record, your current design is tailored to the way you want to see it i.e. in spreadsheet view which is causing your headaches here.

Also using spaces in column names again sure sign of more headaches, while spaces and other special characters are allowed. It is best practice to not use them.


Worse yet, storing stuff to tables you dont want to store. Your solution with a proper design would be easy, though here too it is relatively easy unless you go for the ultimate solution of doing a proper re-design.

Easiest way around this is to use your flawed design to work for you for a change... Below is to give you the basic idea to run one simple query instead of all this overhead. Offcourse you will need to tweak and adjust it.

Select [Employee Name]
      , IIF(equipment1 <Date()+60 ; equipment1; null) Equipment1
      , IIF(equipment2 <Date()+60 ; equipment1; null) Equipment2
      , IIF(equipment3 <Date()+60 ; equipment1; null) Equipment3
      , IIF(equipment4 <Date()+60 ; equipment1; null) Equipment4
From   Yourtable
where
    equipment1 < Date() + 60
or  equipment2 < Date() + 60
or  equipment3 < Date() + 60
or  equipment4 < Date() + 60

namliam

I finally got the time to get to this project. I decided to start simple, with just one piece of equipment to be returned in the query. I figured if I can get the correct code for one piece of equipment, then I can replicate it to the others.

However, when I run the query, it is getting hung up on the first ";" in the query line, (right after Date() +60. When I dig deeper, it defines it as '3075 Error' related to an expression. I think if I can pass this point, then the rest of the query code should fall in line.

Select [Associate],[Badge],[Section]
,IIF (ForktruckLicenseExp<Date()+60 ; ForktruckLicenseExp ; null) ForktruckLicenseExp

Thanks!

 

[namliam]

Might be that the semicolumn needs replacing by a comma

 

[Smokeeater]

It got past the semi-colon issue but is now getting hung up on the end of the expression. If I press OK, it highlights 'ForktruckLicenseExp at the end of the expression. I am only trying one of the license fields until I know I have the code right, then I can replicate it for the others.

Thanks.

 

[namliam]

My bad, to much used to using Oracle or SQL Server,
IIF(equipment1 <Date()+60, equipment1, null) as Equipment1

Or if you are using the designer:
Equipment1: IIF(equipment1 <Date()+60, equipment1, null)

 

[namliam]

[Equip1License], [Equip2License], [Equip3License]
This usually is a sure sign of a design issue or two hidden in the database, a better design would be a table such
EquipmentNumber LicenceDate

And store each licence as a seperate record, your current design is tailored to the way you want to see it i.e. in spreadsheet view which is causing your headaches here.

Please do consider these words as well as those from @Pat Hartman ...

 

[Smokeeater]

I think I have something to work with now! Thank you very much!!

And yes, my plans are to restructure this information into its own separate table. I am not a huge fan of inheriting someone else mistakes (poor db design) but I will get it corrected.

Thanks again!

 

[Smokeeater]

Question...and may be a stupid one at that, but do you know how to add a field to a query that is not based on a table? I want to pass a value from an append query to a table, but it is not based on a field located in a table?

 

[namliam]

what is it based on ?

 

[Smokeeater]

The other fields are coming from a table, but I want to add an identifier for the piece of equipment tied to the licenses. All of those fields are date fields only - I would just like to add an text identifier for each one. I can put this in a query column - Forktruck[], and then we I view it, I have to manually type in Forktruck in the parameter window, and it works fine. I am trying to figure out how to enter "Forktruck" as the query parameter, so it autofills the field, and not have it require manual entry when the query runs. Maybe I am going about this incorrectly?

 

[namliam]

something like:
IIF(equipment1 <Date()+60, equipment1, null) as Forktruk

Or if you are using the designer:
Forktruk: IIF(equipment1 <Date()+60, equipment1, null)

??

 

[Smokeeater]

Yes, you are correct - that will set the identifier I need. I overlooked that. Thanks.