I built this db for our HR department. When I met with them about the reports one of the ones they wanted was based off the 8 Licensure/Certification fields and the date fields associated with them. They need one report that is going to list the Licensure/Certification and the expiration date for that License so they can notify the employee that has or will be expiring. I am not sure I would build a query that is going to reference 8 date fields. Never have tried anything this complex before.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Need your suggestions (1 Viewer)
- Thread starter jmriddic
- Start date
I have defintely written queries where I have done Between[Beginning Date] And [Ending Date] but in this case never for multiple date fields. I do want to get prompted sixteen times when I need to enter it only once.
Hi JMRiddic,
Simple:
Your between functions are all on the same line meaning and between them (check to the left of this line). However, some dates are missing...
On 3 different lines, they wil be ored and it will do the job you're expecting.
JLCantara.
Simple:
Your between functions are all on the same line meaning and between them (check to the left of this line). However, some dates are missing...
On 3 different lines, they wil be ored and it will do the job you're expecting.
JLCantara.
I actually have 8 date fields I need to check because I have 8 text boxes for License/Certification information and each of those date fields are to be associated with each of those. So will access allow me to do that many lines?
I tried what you suggested but now it will not run the query saying its too complex. I will restate the original problem. I need to check 8 license expiration date fields in order to see if any of them fall in the date range I put. I do not need the parameter box to show more than once. Its being suggested to put them on the same line or put them on seperate lines starting on the OR line. Which one is correct?
Attachments
Ok I see what you mean. I ran it as well. Only problem its repeating the employee more than once if it finds matches on any of those fields. If you try 08/01/12 to 08/31/12 you will see what I mean. I just need them to show once. Suggestions?
- Local time
- Today, 12:21
- Joined
- Feb 19, 2002
- Messages
- 43,314
The queries will be simpler if you normalize the schema. You need at least two tables. All the license data needs to move to a second table where you will have one row per license. That means that you don't need to restrict the list to only 8 or waste the space when a person has fewer or none. You would use a subform to display the licenses.
If the licenses are standard, you could have a third table that lists them. Then rather than storing the text, you could pick from a combo and store the ID. This will make the data more consistant so you can produce meaningful reports by license type. To do that now means you need to rely on the keyboard skills of the people doing the data entry.
Also - best practice would be to remove the special characters and embedded spaces from your column names.
If the licenses are standard, you could have a third table that lists them. Then rather than storing the text, you could pick from a combo and store the ID. This will make the data more consistant so you can produce meaningful reports by license type. To do that now means you need to rely on the keyboard skills of the people doing the data entry.
Also - best practice would be to remove the special characters and embedded spaces from your column names.
Similar threads
- Replies
- 21
- Views
- 794
- Replies
- 7
- Views
- 292
- Replies
- 8
- Views
- 398
Users who are viewing this thread
Total: 1 (members: 0, guests: 1)