Question: Dealing with multiple Date field search

[grifter6]

I am working on an employee training db and I need to be able to search entire tables for monthly dates. I have tried using a query and all operators, yet the search returns problems. If I used the "and" operator it only returns employees that have that certain month "May" in each class field, Which most of the dates are different, so data returned is not what Im looking for. The same with the "or" operator, and filters do the same. I need it to return only those fields that have a "May" date for instance. I need a tool that will allow me to search multiple employees with multiple class fields and return only the dates that are in the certain month i am searching for. In the sample I am posting there are only three names and 4 training courses, but the db is much larger than this. I would need to build a search for each month of the year, in each department, and the year is not as important as the Month, for instance "5/**/****" would be fine. What I would like is to be able to search an entire table, and have just the employees and the courses they completed in that particular month return.
I am pretty seasoned at using access, so this is giving me fits. attached to this post is sample of what Im doing, so take a look and tell me if what im tryin to do is even possible, any help would greatly appreciated.

 

[boblarson]

Well, for one you need to normalize your database. You should not have multiple date columns, they should be RECORDS in another table. That will solve your problem.

 

[raskew]

Sure agree with Bob Larson. What you've got is a spreadsheet expressed as an Access table. Suggest, at a minimum:

tblEmployees - Employee Info only
tblClasses - Class Info - One entry for each unique class, i.e. ClassName -Class Date
tblEmployeesClasses - Join table, joining tblEmployees & tblClasses
Relationships joining tblEmployees, tblClasses, tblEmployeesClasses

This can go further, e.g. tblClassNames listing unique class names and joined to tblClasses.

HTH - Bob

 

[grifter6]

so I should give each field-type its own table, but how will that allow me to search through multiple class fields to locate monthly dates for the employees?

 

[nIGHTmAYOR]

Atteached is your solution

So what you needed was a union query creating a virtual table of ids and date instances (Open it in design and try to analyse it syntax)

And then the searcher query which is quite similler to your test query only manages to search in virtualy one column.

What i added up was mean to make the query run a simple input field asking u for the required date to look for so you dont have to make multiple queries.

Feel free to enquire anything that isnt clear.

Best regards,
nIGHTmAYOR

 

[raskew]

Atteached is your solution

-- I don't think so!

Looked at your union query. What is that supposed to be exactly?

Every course date for every employee, all listed under LO/TO??

Not sure how that's helpful and it does nothing towards the primary problem -- an unnormalized database.

Bob

 

[nIGHTmAYOR]

raskew

Well what it's supposed to do is to create a union query never mind what the union field be named (which usualy takes the name of the first field names of the first query) , HOWEVER on the second query , the SEARCHER the output will contain records that had dates of per requested value based on union query.

Mind you this would save him the hassel of redesigning his work for now and still achieve an equal effect advised to him by others.

So finaly you have already pointed that he was wrong on his design and i merily gave him a quick work around for now

(Please let the concerned be the judge )

 

[grifter6]

These classes take place all the time so there is no way to change the setup I have right now. I just need a way to type in a month and only see results for that month, not all the classes. I do appreciate the suggestions and effort but, I just don't think you guys/girls understand what i'm goin for here.

 

[The_Doc_Man]

In a sense, you are quite right. It is your problem. You work with it all the time and you "know" all the ins and outs. We are looking in from afar. That's why it is sometimes very hard for us to productively advise you. In the final analysis, it is your problem and only you can come up with the answer. We merely can point, offer what we believe to be productive suggestions, and hope that there is a nugget in the pile that is like gold for you. But the thread seems to indicate that you either cannot accept our answer or didn't clarify your requirements well enough for us to be more directly targeted.

Don't give up, but take the above comments in mind when posting questions. The less we know about the REAL needs, the worse our suggestions will be. It is the nature of a forum with a limited method of asking and answering questions.

 

[nIGHTmAYOR]

grifter6 , please download my solution and review it

 

[grifter6]

This Db has over two hundred employees and over 900 training courses. They are all separated by departments because one table could not hold all the classes. I have to change the employee info for privacy reasons but I will try to post a better example of what im workin on here.

 

[grifter6]

Here is a larger sample of what I am trying to accomplish. Any questions to clarify please don't hesitate to ask, trust me, i don't take your help lightly. If it was not for sites like this most businesses would still be using rooms full of filing cabinets and hard files.

 

[grifter6]

Nightmayor, I looked at your solution it is pretty good, but it still returns all of the dates under each course instead of just the may ones. what needs to happen is only fields with may dates show up, but where the conflict I am trying to resolve lies, is in that if one employee has a may date and the one below him does not it still shows the one below him because it has to display the entire column to show his may date. I know that sounds confusing here is a visual example.

BAd
Employee ID | Name | training 1 | Training 2 | Training 3 |
5555 smith, John 5/12/2008 6/11/2007 6/17/2008
6666 smith, Jane 4/12/2008 5/12/2008 5/12/2008

In order to show jane smith's two may dates access thinks it has to show the two dates of john's not in may, the same with Jane's april date, I am trying to remedy this so it would look somethin like...

Good
Employee ID | Name | training 1 | Training 2 | Training 3 |
5555 smith, John 5/12/2008 N/A N/a
6666 smith, Jane N/a 5/12/2008 5/12/2008

You guys/girls feelin me?

 

[nIGHTmAYOR]

Oooh , I see , now I get you , but the problem doesn't lay there , your problem lays in having a table with 900 columns for courses. Man seriously its time to normalize your table.
Now its your decision , pick if it's normalizing where I'll help you to achieve it useing the aid of vba or the union query where you will have to type in 900 queries of unions.

 

[boblarson]

You guys/girls feelin me?

No, after looking at your database all I can say is you really, really need to learn normalization principles. This is spreadsheet thinking and not RELATIONAL DATABASE thinking. Your problem is going to be insurmountable, or at the very least will require so much more work to "band-aid fix" it so that you can get the data back out that you are going to go nuts (and I fear us as well).

I am attaching a quick sample I made over lunchtime (instead of eating) which basically shows how it SHOULD be done. If it is done that way then there is NO problem in getting the data out.

One of your major hurdles in the current database is that your "date" fields are not date fields at all and in fact you don't even store just dates in them, but text, dates, text AND dates.

So, I personally think you should go back and redesign this to be a relational database model because your current design is just not going to be something that is maintainable in the long term (and possibly not even in the short term).

Sorry if that comes off sounding bad or mean. It isn't intended that way, but it is intended to give you a reality check.

 

[grifter6]

Lets say I go back in and redesign the database. I still would need to use text in the fields because I am using a standard Form for all employees in each department. Why? because otherwise I would have to make a training form for each individual employee, by using text I can use dates, search them, and list if an employee does not require training, and have code in place to tell me when the training becomes void after a year. Yet I would be interested in redesigning, this is the third version of the database over as many years. Each time I have made improvements in the form display and data storage as our company has grown exponentially. If you want to elaborate a little more on normalization I would appreciate it and begin to go ahead and work on a new version for january09.