Help filtering query

[nut75195]

Hi everyone,

I'm pretty new to Access and I was hoping that someone might be able to point me in the right direction. I have a table that contains a bunch of employees and lists out expiration dates for various items they have (badges, passports, etc). My end goal is to have something easy for the company to track items that are expiring soon. I have a form that allows you to enter the number (in days) for how far out you want to check. For example, if you wanted to see everyone with passports expiring in 60 days you could enter 60 and click "run query" and it would show a report with passports expiring within 60 days. That seems pretty straight forward but I would like to have a form where a user can enter how many days out for each of the items (like below) and it would search through all the items but only display the person’s name, item that is about to expire and expiration date. For example, if John Smith had a badge expiring in 30 days and a LOA expiring in 80 days then it would list John Smith with those two items and the dates that they are going to expire. I am not even sure if this is possible because I haven't been able to find anything online on how to do it. The information I am looking for is how to filter and display the results correctly and also how to search for the dates correctly (example of how I am currently doing it is below). I was trying to attach the database here but can't seem to get it to upload. Any help is greatly appreciated.

Item Days out
Passport 60
Visa 60
Badge 60
LOA 90
eCC 90
Med 180
IFAM 180


FACT 180

I am using this criteria to check for the expiring dates: (but it doesn't seem to be working correctly)

>=Date()+[Forms]![frm_Test]![PriPassportExpirationInput]

[nut75195]

Tester.zip

I was finally able to upload the database as a zip file.

[June7]

Criteria needs to be like:

Between Date() And Date()+Nz([Forms]![frm_Test]![FACTExpirationInput],0)

[nut75195]

Criteria needs to be like:

Between Date() And Date()+Nz([Forms]![frm_Test]![FACTExpirationInput],0)

June7,

Thank you for your help. That worked perfect for the date search criteria. Now if I can just figure out the filtering I will be good to go.

[June7]

What else do you need to figure out?

[nut75195]

What else do you need to figure out?

If this is possible, I am trying to only list the items that are expired. So within table John Smith has 9 different items that can expire. I would like the report to only list the name and expiration date of the item(s) that are expiring. Right now if any of those items match it will list all of the items he has rather than just listing the single items that will expire. Would this be controlled through the reports after the query is made?

[June7]

Your data structure is going to make what you want difficult if not impossible. Because you have fields for each document type and its expiration date, if any one matches criteria, of course the entire record will be retrieved. I am not sure what else you would expect to happen. A more normalized structure would involve another table that has a record for each document. That structure would allow retrieving specific documents.

ID	EmpID	DocType	ExpDate
1	72	Passport	2/21/2019
2	72	Med	9/15/2019

[nut75195]

Your data structure is going to make what you want difficult if not impossible. Because you have fields for each document type and its expiration date, if any one matches criteria, of course the entire record will be retrieved. I am not sure what else you would expect to happen. A more normalized structure would involve another table that has a record for each document. That structure would allow retrieving specific documents.

ID	EmpID	DocType	ExpDate
1	72	Passport	2/21/2019
2	72	Med	9/15/2019

Thank you for the help and I apologize for such newbie questions. So to normalize this correctly and to be able to do a query like that, would I add only one other table with all the items employees items or would I create one table for each item type? and I know I would link them by Employee ID but what type of relationship would be between the tables (like one-to-one or one-to-many)? I've read so much stuff on normalizing tables but I'm still not sure what would be best for this situation. Again thanks for all your help.

[June7]

One table as described.

Many-to-many. Each employee can have multiple types and each type can associate with many employees. The suggested table is a 'junction' between employees and types.

It is a balancing act between normalization and ease of data entry/output. Normalize until it hurts, denormalize until it works.

If you want to provide db, follow instructions at bottom of my post.

[nut75195]

One table as described.

Many-to-many. Each employee can have multiple types and each type can associate with many employees. The suggested table is a 'junction' between employees and types.

It is a balancing act between normalization and ease of data entry/output. Normalize until it hurts, denormalize until it works.

If you want to provide db, follow instructions at bottom of my post.

I've attached the database here. I thought I was on a good start because I created a new table called Items and then joined the two tables with a junction table but I guess I'm not doing something right. Database.zip

[June7]

The suggestion would eliminate Items table.

Employees
EmpID (autonumber primary key)
EmpPositionNumber
etc

DocTypes
DocID (autonumber primary key)
DocDesc (Passport, Visa, etc)

EmployeeDocs
EmpID_FK
DocID_FK (However, if you prefer to save the actual document description instead of DocID, make this a text field and eliminate DocID from the DocTypes lookup table.)
DocNumber
IssueDate
ExpDate

[nut75195]

The suggestion would eliminate Items table.

Employees
EmpID (autonumber primary key)
EmpPositionNumber
etc

DocTypes
DocID (autonumber primary key)
DocDesc (Passport, Visa, etc)

EmployeeDocs
EmpID_FK
DocID_FK (However, if you prefer to save the actual document description instead of DocID, make this a text field and eliminate DocID from the DocTypes lookup table.)
DocNumber
IssueDate
ExpDate

Thanks for all the help. If you happen to have the time could you look at it one more time? I promise this is the last time I think I followed your guidance above pretty well but I am obviously missing something because in my frm_Employee_Details form the name is giving me #Name? errors. Not sure if it is a relationship issue or something I am missing with the tables. Database2.zip

[June7]

Cannot directly reference a table like that in ControlSource property. You need a subform bound to EmployeeDocs. Build a form for EmployeeDocs. Now drag that form onto the Employees form (the 'main' form). Or drag the SubformContainerControl from design tab and set its SourceObject property.

However, you did not build EmployeeDocs as I suggested. What you have is the same structure you started with. There should not be a field for each document type. Each document should be a separate record as shown in post7.

[nut75195]

Cannot directly reference a table like that in ControlSource property. You need a subform bound to EmployeeDocs. Build a form for EmployeeDocs. Now drag that form onto the Employees form (the 'main' form). Or drag the SubformContainerControl from design tab and set its SourceObject property.

However, you did not build EmployeeDocs as I suggested. What you have is the same structure you started with. There should not be a field for each document type. Each document should be a separate record as shown in post7.

Sorry about that. I think I fixed it now. I just built the tables exactly like you said in post 11 and the query for employees and their documents is working good. I also changed the subform to what I believe is bound to the EmployeeDocs. Using this new data structure, how do I get the new sub form to populate the boxes based on the correct doc types? Right now they are just all linking to the same one item and I cant seem to correct it. Database3.zip

[June7]

Now that you have EmployeeDocs normalized, Tab control is not useful because there is not a field for each document type. Remove Tab control and place data controls directly on form and set frm_EmployeeDocs as Continuous or Datasheet. Use a combobox to select document type.