Search results

I'm using Access 2010. Explanation of tables: tblCACs = the workers tblConReq (consult requests) = main table that serves to supply a main form tblConReq_CPRSStaff = serves to supply as a subform to main form tblConReq_MedRecs = same as above tblConReq_QualMang = same as above...

Oh, sorry man. You must have read me wrong in the second paragraph above. A date range for CompletedDate is not something I want but know how to do. What I really want is a date range for DueDate instead. Kinda tough to figure this out.

I'm going to give this a try right now. Is there any way to prompt the user for a date range instead of having to write so many queries though?

Yes; I've had the "Between" code in the criteria line for the completion dates before but it doesn't return the correct results. For example, if an employee completed training module A on 11/26/14 & I wanted to see who would be due for November 2015, the "Between" code wouldn't show me module A...

My database consists of tables with employee info, where they work, training modules, & the respective junction tables. For the training modules, there are employee completion dates for all of them. Naturally, I wrote a query that calculates the due dates for each module as follows (for...

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

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...

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...

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.

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...

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

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

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?

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...

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...

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...

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.)?

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...

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

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...