Where clause on a Look-up field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all

I am running a query that uses the WHERE Clase on a lookup field. The lookup
field contains lookup dates. I have noticed that they are not stored as
dates. Coud anyone help in figuring this one out please?

Thanks
 
Alex

From your description, you have a table that holds a "lookup" type field. A
scan through the tablesdbdesign newsgroup, and a look at mvps.org/access
will show a strong consensus against using lookup fields in tables. One
reason why is just what you've described.

The problem you're running into is that what is stored, and what appears,
are not the same. If you want to look up/query a date of, say, 3/31/2006,
you might have to (know, remember, discover) that you have to use DateID =
237 or some such.

The quick/dirty solution is to join the date lookup table (where the dates
are coming from) and do your query criteria of actual dates against the
actual date field in that lookup dates table.

A more permanent solution is to change the field-type in the main table to
whatever matches your lookup date table's ID data type. Then, what you see
is what you get. But you still would want to join to the lookup date table
to be able to search by actual date.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Hi Jeff

Thanks. I am trying the quick and dirty way first and this is the code I
have and it is not working:


INSERT INTO ArchiveHolidayNotTaken
SELECT HolidayNotTaken.*
FROM ((Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN Holidays ON HolidayNotTaken.HolDate =
Holidays.Date) INNER JOIN [Temporary] ON HolidayNotTaken.EmployeeID =
Temporary.EmployeeID
WHERE (((Holidays.Date)<=([employees].[archivecalcdate])));

A quick explanation :
Holidays Table: contains the real dates
HolNotTaken Table: contains the lookup
HolNotTaken is a joined table with Employees
Temporary is a temp Table (I know that's obvious) containing the records to
work with

Thanks for your help in advance
 
Alex

Consider stepping back... If you make this just a select query, does find
what you want?

When that part's working, move back to working out the INSERT portion.

What happens when it "doesn't work"?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


alex said:
Hi Jeff

Thanks. I am trying the quick and dirty way first and this is the code I
have and it is not working:


INSERT INTO ArchiveHolidayNotTaken
SELECT HolidayNotTaken.*
FROM ((Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN Holidays ON HolidayNotTaken.HolDate =
Holidays.Date) INNER JOIN [Temporary] ON HolidayNotTaken.EmployeeID =
Temporary.EmployeeID
WHERE (((Holidays.Date)<=([employees].[archivecalcdate])));

A quick explanation :
Holidays Table: contains the real dates
HolNotTaken Table: contains the lookup
HolNotTaken is a joined table with Employees
Temporary is a temp Table (I know that's obvious) containing the records to
work with

Thanks for your help in advance

Jeff Boyce said:
Alex

From your description, you have a table that holds a "lookup" type field. A
scan through the tablesdbdesign newsgroup, and a look at mvps.org/access
will show a strong consensus against using lookup fields in tables. One
reason why is just what you've described.

The problem you're running into is that what is stored, and what appears,
are not the same. If you want to look up/query a date of, say, 3/31/2006,
you might have to (know, remember, discover) that you have to use DateID =
237 or some such.

The quick/dirty solution is to join the date lookup table (where the dates
are coming from) and do your query criteria of actual dates against the
actual date field in that lookup dates table.

A more permanent solution is to change the field-type in the main table to
whatever matches your lookup date table's ID data type. Then, what you see
is what you get. But you still would want to join to the lookup date table
to be able to search by actual date.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Click to expand...
Click to expand...
 
Jeff/Alex:
Same problem here. We had used a Sellect/Lookup fields so that we could
give our entry person the abillity to add sellection items without having to
open the form in design view and edit the list in the form. We also wanted
the abillity to sort the list according to a numbers in the lookup table so
that the most used entries could appear first. I'm recreating the form with
lists instead now but want the data out of the data table to put into a new
non-lookup table. You suggested a join to get what you see out. Can you
give more detail on this? We have an old data table, a new one and 2 lookup
tables for 2 different lookup fields. Any suggestions for doing the combo
list boxes differently?

Jeff Boyce said:
Alex

Consider stepping back... If you make this just a select query, does find
what you want?

When that part's working, move back to working out the INSERT portion.

What happens when it "doesn't work"?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


alex said:
Hi Jeff

Thanks. I am trying the quick and dirty way first and this is the code I
have and it is not working:


INSERT INTO ArchiveHolidayNotTaken
SELECT HolidayNotTaken.*
FROM ((Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN Holidays ON HolidayNotTaken.HolDate =
Holidays.Date) INNER JOIN [Temporary] ON HolidayNotTaken.EmployeeID =
Temporary.EmployeeID
WHERE (((Holidays.Date)<=([employees].[archivecalcdate])));

A quick explanation :
Holidays Table: contains the real dates
HolNotTaken Table: contains the lookup
HolNotTaken is a joined table with Employees
Temporary is a temp Table (I know that's obvious) containing the records to
work with

Thanks for your help in advance

Jeff Boyce said:
Alex

From your description, you have a table that holds a "lookup" type field. A
scan through the tablesdbdesign newsgroup, and a look at mvps.org/access
will show a strong consensus against using lookup fields in tables. One
reason why is just what you've described.

The problem you're running into is that what is stored, and what appears,
are not the same. If you want to look up/query a date of, say, 3/31/2006,
you might have to (know, remember, discover) that you have to use DateID =
237 or some such.

The quick/dirty solution is to join the date lookup table (where the dates
are coming from) and do your query criteria of actual dates against the
actual date field in that lookup dates table.

A more permanent solution is to change the field-type in the main table to
whatever matches your lookup date table's ID data type. Then, what you see
is what you get. But you still would want to join to the lookup date table
to be able to search by actual date.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Hi all

I am running a query that uses the WHERE Clase on a lookup field. The
lookup
field contains lookup dates. I have noticed that they are not stored as
dates. Coud anyone help in figuring this one out please?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Jeff

As per tyour advice I stepped back (way back)

Simple Select, no Crieria, gives me all records in HolidayNotTaken that
have a record in Temporary.
SELECT HolidayNotTaken.*
FROM (Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN [Temporary] ON
HolidayNotTaken.EmployeeID = Temporary.EmployeeID;

Simple Select wih Criteria comparing the lookup field and the date field in
Employees gives me the exact same thing.
SELECT HolidayNotTaken.*
FROM (Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN [Temporary] ON
HolidayNotTaken.EmployeeID = Temporary.EmployeeID
WHERE (((HolidayNotTaken.HolDate)<=([employees].[archivecalcdate])));

Simple Select, no Critera, With the Holiday table, that contains the dates
gives nothing.
SELECT HolidayNotTaken.*
FROM ((Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN [Temporary] ON
HolidayNotTaken.EmployeeID = Temporary.EmployeeID) INNER JOIN Holidays ON
HolidayNotTaken.HolDate = Holidays.Date;


Simple Select with Critera With the Holiday table comparing the date field
of the Holiday table that contains the dates with the date field inthe
Employees table gives nothing.
INSERT INTO ArchiveHolidayNotTaken
SELECT HolidayNotTaken.*
FROM ((Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN Holidays ON HolidayNotTaken.HolDate =
Holidays.Date) INNER JOIN [Temporary] ON HolidayNotTaken.EmployeeID =
Temporary.EmployeeID
WHERE (((Holidays.Date)<=([employees].[archivecalcdate])));


Hope these steps help.


Jeff Boyce said:
Alex

Consider stepping back... If you make this just a select query, does find
what you want?

When that part's working, move back to working out the INSERT portion.

What happens when it "doesn't work"?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


alex said:
Hi Jeff

Thanks. I am trying the quick and dirty way first and this is the code I
have and it is not working:


INSERT INTO ArchiveHolidayNotTaken
SELECT HolidayNotTaken.*
FROM ((Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN Holidays ON HolidayNotTaken.HolDate =
Holidays.Date) INNER JOIN [Temporary] ON HolidayNotTaken.EmployeeID =
Temporary.EmployeeID
WHERE (((Holidays.Date)<=([employees].[archivecalcdate])));

A quick explanation :
Holidays Table: contains the real dates
HolNotTaken Table: contains the lookup
HolNotTaken is a joined table with Employees
Temporary is a temp Table (I know that's obvious) containing the records to
work with

Thanks for your help in advance

Jeff Boyce said:
Alex

From your description, you have a table that holds a "lookup" type field. A
scan through the tablesdbdesign newsgroup, and a look at mvps.org/access
will show a strong consensus against using lookup fields in tables. One
reason why is just what you've described.

The problem you're running into is that what is stored, and what appears,
are not the same. If you want to look up/query a date of, say, 3/31/2006,
you might have to (know, remember, discover) that you have to use DateID =
237 or some such.

The quick/dirty solution is to join the date lookup table (where the dates
are coming from) and do your query criteria of actual dates against the
actual date field in that lookup dates table.

A more permanent solution is to change the field-type in the main table to
whatever matches your lookup date table's ID data type. Then, what you see
is what you get. But you still would want to join to the lookup date table
to be able to search by actual date.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Hi all

I am running a query that uses the WHERE Clase on a lookup field. The
lookup
field contains lookup dates. I have noticed that they are not stored as
dates. Coud anyone help in figuring this one out please?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Alex

I'm not sure, but wouldn't you expect the second query (the SELECT with
criteria) to produce different results? You'd have to do some experimenting
with that query to figure out what it takes to get it to return the records
you expect.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


alex said:
Jeff

As per tyour advice I stepped back (way back)

Simple Select, no Crieria, gives me all records in HolidayNotTaken that
have a record in Temporary.
SELECT HolidayNotTaken.*
FROM (Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN [Temporary] ON
HolidayNotTaken.EmployeeID = Temporary.EmployeeID;

Simple Select wih Criteria comparing the lookup field and the date field in
Employees gives me the exact same thing.
SELECT HolidayNotTaken.*
FROM (Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN [Temporary] ON
HolidayNotTaken.EmployeeID = Temporary.EmployeeID
WHERE (((HolidayNotTaken.HolDate)<=([employees].[archivecalcdate])));

Simple Select, no Critera, With the Holiday table, that contains the dates
gives nothing.
SELECT HolidayNotTaken.*
FROM ((Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN [Temporary] ON
HolidayNotTaken.EmployeeID = Temporary.EmployeeID) INNER JOIN Holidays ON
HolidayNotTaken.HolDate = Holidays.Date;


Simple Select with Critera With the Holiday table comparing the date field
of the Holiday table that contains the dates with the date field inthe
Employees table gives nothing.
INSERT INTO ArchiveHolidayNotTaken
SELECT HolidayNotTaken.*
FROM ((Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN Holidays ON HolidayNotTaken.HolDate =
Holidays.Date) INNER JOIN [Temporary] ON HolidayNotTaken.EmployeeID =
Temporary.EmployeeID
WHERE (((Holidays.Date)<=([employees].[archivecalcdate])));


Hope these steps help.


Jeff Boyce said:
Alex

Consider stepping back... If you make this just a select query, does find
what you want?

When that part's working, move back to working out the INSERT portion.

What happens when it "doesn't work"?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


alex said:
Hi Jeff

Thanks. I am trying the quick and dirty way first and this is the code I
have and it is not working:


INSERT INTO ArchiveHolidayNotTaken
SELECT HolidayNotTaken.*
FROM ((Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN Holidays ON
Click to expand...
HolidayNotTaken.HolDate
=
Holidays.Date) INNER JOIN [Temporary] ON HolidayNotTaken.EmployeeID =
Temporary.EmployeeID
WHERE (((Holidays.Date)<=([employees].[archivecalcdate])));

A quick explanation :
Holidays Table: contains the real dates
HolNotTaken Table: contains the lookup
HolNotTaken is a joined table with Employees
Temporary is a temp Table (I know that's obvious) containing the
Click to expand...
records
to
work with

Thanks for your help in advance

:

Alex

From your description, you have a table that holds a "lookup" type field. A
scan through the tablesdbdesign newsgroup, and a look at mvps.org/access
will show a strong consensus against using lookup fields in tables. One
reason why is just what you've described.

The problem you're running into is that what is stored, and what appears,
are not the same. If you want to look up/query a date of, say, 3/31/2006,
you might have to (know, remember, discover) that you have to use
Click to expand...
DateID
=
237 or some such.

The quick/dirty solution is to join the date lookup table (where the dates
are coming from) and do your query criteria of actual dates against the
actual date field in that lookup dates table.

A more permanent solution is to change the field-type in the main
Click to expand...
table
to
whatever matches your lookup date table's ID data type. Then, what
Click to expand...
you
see
is what you get. But you still would want to join to the lookup
Click to expand...
date
table
to be able to search by actual date.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Hi all

I am running a query that uses the WHERE Clase on a lookup field. The
lookup
field contains lookup dates. I have noticed that they are not
Click to expand...
stored
as
dates. Coud anyone help in figuring this one out please?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Sorry, but that's way too general a description for me to be able to offer
any specific suggestions.

Are you saying you want to use a list from a table (i.e., a "lookup" table)
in a form? If so, you could use a combo box or a list box in the form to
display the "choices" from the lookup table.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


mrklaxon said:
Jeff/Alex:
Same problem here. We had used a Sellect/Lookup fields so that we could
give our entry person the abillity to add sellection items without having to
open the form in design view and edit the list in the form. We also wanted
the abillity to sort the list according to a numbers in the lookup table so
that the most used entries could appear first. I'm recreating the form with
lists instead now but want the data out of the data table to put into a new
non-lookup table. You suggested a join to get what you see out. Can you
give more detail on this? We have an old data table, a new one and 2 lookup
tables for 2 different lookup fields. Any suggestions for doing the combo
list boxes differently?

Jeff Boyce said:
Alex

Consider stepping back... If you make this just a select query, does find
what you want?

When that part's working, move back to working out the INSERT portion.

What happens when it "doesn't work"?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


alex said:
Hi Jeff

Thanks. I am trying the quick and dirty way first and this is the code I
have and it is not working:


INSERT INTO ArchiveHolidayNotTaken
SELECT HolidayNotTaken.*
FROM ((Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN Holidays ON
Click to expand...
HolidayNotTaken.HolDate
=
Holidays.Date) INNER JOIN [Temporary] ON HolidayNotTaken.EmployeeID =
Temporary.EmployeeID
WHERE (((Holidays.Date)<=([employees].[archivecalcdate])));

A quick explanation :
Holidays Table: contains the real dates
HolNotTaken Table: contains the lookup
HolNotTaken is a joined table with Employees
Temporary is a temp Table (I know that's obvious) containing the
Click to expand...
records
to
work with

Thanks for your help in advance

:

Alex

From your description, you have a table that holds a "lookup" type field. A
scan through the tablesdbdesign newsgroup, and a look at mvps.org/access
will show a strong consensus against using lookup fields in tables. One
reason why is just what you've described.

The problem you're running into is that what is stored, and what appears,
are not the same. If you want to look up/query a date of, say, 3/31/2006,
you might have to (know, remember, discover) that you have to use
Click to expand...
DateID
=
237 or some such.

The quick/dirty solution is to join the date lookup table (where the dates
are coming from) and do your query criteria of actual dates against the
actual date field in that lookup dates table.

A more permanent solution is to change the field-type in the main
Click to expand...
table
to
whatever matches your lookup date table's ID data type. Then, what
Click to expand...
you
see
is what you get. But you still would want to join to the lookup
Click to expand...
date
table
to be able to search by actual date.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Hi all

I am running a query that uses the WHERE Clase on a lookup field. The
lookup
field contains lookup dates. I have noticed that they are not
Click to expand...
stored
as
dates. Coud anyone help in figuring this one out please?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeff

As with everything else it is sometimes trial and error and going back to
step one and so on. I discovered that the table HolidaysNotTaken had bad keys
so it didn't know what to show. Cleaned the keys and hey presto we're
working.

Thank you for your help on this

Jeff Boyce said:
Alex

I'm not sure, but wouldn't you expect the second query (the SELECT with
criteria) to produce different results? You'd have to do some experimenting
with that query to figure out what it takes to get it to return the records
you expect.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


alex said:
Jeff

As per tyour advice I stepped back (way back)

Simple Select, no Crieria, gives me all records in HolidayNotTaken that
have a record in Temporary.
SELECT HolidayNotTaken.*
FROM (Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN [Temporary] ON
HolidayNotTaken.EmployeeID = Temporary.EmployeeID;

Simple Select wih Criteria comparing the lookup field and the date field in
Employees gives me the exact same thing.
SELECT HolidayNotTaken.*
FROM (Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN [Temporary] ON
HolidayNotTaken.EmployeeID = Temporary.EmployeeID
WHERE (((HolidayNotTaken.HolDate)<=([employees].[archivecalcdate])));

Simple Select, no Critera, With the Holiday table, that contains the dates
gives nothing.
SELECT HolidayNotTaken.*
FROM ((Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN [Temporary] ON
HolidayNotTaken.EmployeeID = Temporary.EmployeeID) INNER JOIN Holidays ON
HolidayNotTaken.HolDate = Holidays.Date;


Simple Select with Critera With the Holiday table comparing the date field
of the Holiday table that contains the dates with the date field inthe
Employees table gives nothing.
INSERT INTO ArchiveHolidayNotTaken
SELECT HolidayNotTaken.*
FROM ((Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN Holidays ON HolidayNotTaken.HolDate =
Holidays.Date) INNER JOIN [Temporary] ON HolidayNotTaken.EmployeeID =
Temporary.EmployeeID
WHERE (((Holidays.Date)<=([employees].[archivecalcdate])));


Hope these steps help.


Jeff Boyce said:
Alex

Consider stepping back... If you make this just a select query, does find
what you want?

When that part's working, move back to working out the INSERT portion.

What happens when it "doesn't work"?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Hi Jeff

Thanks. I am trying the quick and dirty way first and this is the code I
have and it is not working:


INSERT INTO ArchiveHolidayNotTaken
SELECT HolidayNotTaken.*
FROM ((Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN Holidays ON HolidayNotTaken.HolDate
=
Holidays.Date) INNER JOIN [Temporary] ON HolidayNotTaken.EmployeeID =
Temporary.EmployeeID
WHERE (((Holidays.Date)<=([employees].[archivecalcdate])));

A quick explanation :
Holidays Table: contains the real dates
HolNotTaken Table: contains the lookup
HolNotTaken is a joined table with Employees
Temporary is a temp Table (I know that's obvious) containing the records
to
work with

Thanks for your help in advance

:

Alex

From your description, you have a table that holds a "lookup" type
field. A
scan through the tablesdbdesign newsgroup, and a look at mvps.org/access
will show a strong consensus against using lookup fields in tables. One
reason why is just what you've described.

The problem you're running into is that what is stored, and what
appears,
are not the same. If you want to look up/query a date of, say,
3/31/2006,
you might have to (know, remember, discover) that you have to use DateID
=
237 or some such.

The quick/dirty solution is to join the date lookup table (where the
dates
are coming from) and do your query criteria of actual dates against the
actual date field in that lookup dates table.

A more permanent solution is to change the field-type in the main table
to
whatever matches your lookup date table's ID data type. Then, what you
see
is what you get. But you still would want to join to the lookup date
table
to be able to search by actual date.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Hi all

I am running a query that uses the WHERE Clase on a lookup field. The
lookup
field contains lookup dates. I have noticed that they are not stored
as
dates. Coud anyone help in figuring this one out please?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top