Question about searching for one date accross multiple date fields. Thanks!

[bookingdb]

I am currently building a booking and invoicing database for the small business where I work. One aspect of this database allows you to enter an employee name and schedule days off via a form I have already built. To access this information I would like a parameter query where you can enter a date once and then have this date looked up across a number of date fields. Here are my table column headings:

ID
Employee Name
Date 1
Date 2
Date 3
Date 4
Date 5
Date 6
Date 7
....etc

So, basically, the parameter query would ask "What date?" which the person would enter, and then search all 12 date fields to see if the date was contained in any one of them. Then it would pull any records which did have the date in one of the fields.

It seems really simple but I have searched and searched and can't seem to find the answer. Tried a multivalue field but does not seem to work.

Thanks very much, any help appreciated! Elspeth

 

[boblarson]

First of all, your table is not normalized (you should be thinking narrow and long and not like a spreadsheet - short and wide). You should NOT have multiple date columns for each user, UNLESS these date columns are totally different than the other date columns. If they are similar, or the same but just for allowing multiple date values for a record, then you should have another table tied to the other record's ID. Then you can do your search with ease. Otherwise it is not a simple, straightforward matter.

 

[KeithG]

Firstly I would like to say that the way your table is setup up is in proper design. You have a violation of the First Normal Form It should be broken into an Employees table and a Date table with a One to Many relationship from the Employees table to the Date table. But anyways something like belwo should work for you.

[Name Of Your Table]=Replace this with the name of you table

Select * From [Name Of Your Table] Where Date1=[Enter Date] or Date2=[Enter Date] or Date3=[Enter Date] or Date4=[Enter Date] or Date5=[Enter Date] or Date6=[Enter Date] or Date7=[Enter Date]

 

[bookingdb]

OK. thanks very much!

However, when I tried adding the above query in SQL view it did not seemed to work. It pulled up all the records and does not prompt the user for a date.....

Am I doing something really obviously wrong?! Sorry!

Elspeth

 

[KeithG]

Add a parameter with a data type date/time named Enter Date. On the toolbar goto Query=>Parameters

 

[bookingdb]

OK, so I added this parameter. I also filled out this query using the actual headings on my table and the correct table name. The SQL view looks like this:

PARAMETERS [Enter Date] DateTime;
SELECT *
FROM [Reminder and Ongoing Events Table]
WHERE 'Popup Date 1=[Enter Date] or Popup Date 2=[Enter Date] or Popup Date 3=[Enter Date] or Popup Date 4=[Enter Date] or Popup Date 5=[Enter Date] or Popup Date 6=[Enter Date] or Popup Date 7=[Enter Date] or Popup Date 8=[Enter Date] or Popup Date 9=[Enter Date] or Popup Date 10=[Enter Date] or Popup Date 11=[Enter Date] or Popup Date 12=[Enter Date]';

It saves fine and prompts for "Enter Date" but when I type in a specific date it does not narrow the records pulled and just pulls all the records from the table.

What do you think? I am sorry if this is due to my silliness - I am trying hard to learn how to get this stuff right. All help so far much appreciated!

Elspeth

 

[bookingdb]

Any ideas anyone!?

 

[neileg]

You have single quotes surrounding your WHERE clause. Take them out and try again.

 

[Rich]

I am trying hard to learn how to get this stuff right. Elspeth

Then take the advice already given and normalise your db

 

[bookingdb]

I did try that previously but it didn't seem to work. I have tried removing them now so that it looks like this:

PARAMETERS [Enter Date] DateTime;
SELECT *
FROM [Reminder and Ongoing Events Table]
WHERE Popup Date 1=[Enter Date] or Popup Date 2=[Enter Date] or Popup Date 3=[Enter Date] or Popup Date 4=[Enter Date] or Popup Date 5=[Enter Date] or Popup Date 6=[Enter Date] or Popup Date 7=[Enter Date] or Popup Date 8=[Enter Date] or Popup Date 9=[Enter Date] or Popup Date 10=[Enter Date] or Popup Date 11=[Enter Date] or Popup Date 12=[Enter Date];

but it will now not let me save this. Msg reads:

syntax error (missing operator) in query expression 'Popup Date 1=[Enter Date] or Popup Date 2=[Enter Date] or Popup Date 3=[Enter Date] or Popup Date 4=[Enter Date] or Popup Date 5=[Enter Date] or Popup Date 6=[Enter Date] or Popup Date 7=[Enter Date] or Popup Date 8=[Enter Date] or Popup Date 9=[Enter Date] or Popup Date 10=[Enter Date] or Popup Date 11=[Enter Date] or Popup Date 12=[Enter Date]'.

 

[bookingdb]

Thanks for any help

 

[bookingdb]

Then take the advice already given and normalise your db

I would like to do this - just not sure how exactly (?!)

 

[neileg]

syntax error (missing operator) in query expression 'Popup Date 1=[Enter Date] or Popup Date 2=[Enter Date] or Popup Date 3=[Enter Date] or Popup Date 4=[Enter Date] or Popup Date 5=[Enter Date] or Popup Date 6=[Enter Date] or Popup Date 7=[Enter Date] or Popup Date 8=[Enter Date] or Popup Date 9=[Enter Date] or Popup Date 10=[Enter Date] or Popup Date 11=[Enter Date] or Popup Date 12=[Enter Date]'.

Probably need to surround the field names with square brackets. You shouldn't have spaces or punctuation in object names.

To normalise your structure, you need to have one table with employee details and one with the days off. Each day off record will hold the id of the relevant employee.

 

[russi]

Hi.

I am in a similar predicament to yours.
Did you ever find/get a solution?

Russ

 

[bookingdb]

Hi.

I am in a similar predicament to yours.
Did you ever find/get a solution?

Russ

I did what all the posters advised and normalised the process instead. Created two tables. The first table had the following headings:

EMPLOYEE -EMPLOYEE ID
David -DA
Sheila -SH
Rachel -RA
Eric -ER
Nigel -NI

Under which I listed the employees and assigned ID's as above. Then I created a second table with two more fields and entered info like this:

EMPLOYEE ID -DATE
DA -04/07/07
DA -08/09/07
SH -06/12/08
SH -08/09/07
NI -08/04/08

Then created a relationship between the employee ID in both tables.

This then allowed me to build a query that pulled employee ID, date and employee name. I then added the criteria in the date column which was [which date?] and so the query now prompts for a date and then pulls everyone who is off that date.

It wasn't as complicated as I thought - I should have just done this in the first place! Thanks for everyone's help though....

Elspeth

 

[russi]

Thanks. This is helpful, although I will need to come up with some modificartions,as any given ssn might or might not exist in a given table. Same with the date fields...

I am sincerely happy that it helped you.

Russ

 

[neileg]

Elspeth

Your employee ID is going to get you into trouble When you employ someone with the same initials. Better to use an autonumber.