Querying by multiple dates

  • Thread starter Thread starter Jamesbfagan
  • Start date Start date
J

Jamesbfagan

We are an insurance company that has 5-10 sold dates per client. How in the
world do I (newbie) query on date 1, 2 3 etc?
 
In design view grid criteria row enter --
#1/1/2008# OR #2/3/2007# OR #5/9/2008#
In SQL --
[YourDate] = #1/1/2008# OR [YourDate] = #2/3/2007# OR [YourDate] =
#5/9/2008#
 
I understand writing this in the row, but it would only be good for "one" of
the columns dates. We can go out to a house up to 5 times and sell something
In design view grid criteria row enter --
#1/1/2008# OR #2/3/2007# OR #5/9/2008#
In SQL --
[YourDate] = #1/1/2008# OR [YourDate] = #2/3/2007# OR [YourDate] =
#5/9/2008#
--
KARL DEWEY
Build a little - Test a little


Jamesbfagan said:
We are an insurance company that has 5-10 sold dates per client. How in the
world do I (newbie) query on date 1, 2 3 etc?
Click to expand...
Click to expand...
 
Yes for each column but on a different criteria row.
You should have a record for each visit, not a column per visit.
Use a union query to align your data the correct way ---
SELECT Field1, Field2, Date1 AS VisitDate
From YourTable
UNION ALL SELECT Field1, Field2, Date2 AS VisitDate
From YourTable
UNION ALL SELECT Field1, Field2, Date3 AS VisitDate
From YourTable
UNION ALL SELECT Field1, Field2, Date4 AS VisitDate
From YourTable
UNION ALL SELECT Field1, Field2, Date5 AS VisitDate
From YourTable;

--
KARL DEWEY
Build a little - Test a little


Jamesbfagan said:
I understand writing this in the row, but it would only be good for "one" of
the columns dates. We can go out to a house up to 5 times and sell something
In design view grid criteria row enter --
#1/1/2008# OR #2/3/2007# OR #5/9/2008#
In SQL --
[YourDate] = #1/1/2008# OR [YourDate] = #2/3/2007# OR [YourDate] =
#5/9/2008#
--
KARL DEWEY
Build a little - Test a little


Jamesbfagan said:
We are an insurance company that has 5-10 sold dates per client. How in the
world do I (newbie) query on date 1, 2 3 etc?
Click to expand...
Click to expand...
Click to expand...
 
Hi Karl, (I'm very new to this) would you mind breaking this down for me so a
kid can understand it? I think I'm going all over the map trying to figure
out what you mean by 1 record per visit and not column per visit. And I'm
not to sure what the field 1 and 2 and then visit date. Thank you for your
help.

KARL DEWEY said:
Yes for each column but on a different criteria row.
You should have a record for each visit, not a column per visit.
Use a union query to align your data the correct way ---
SELECT Field1, Field2, Date1 AS VisitDate
From YourTable
UNION ALL SELECT Field1, Field2, Date2 AS VisitDate
From YourTable
UNION ALL SELECT Field1, Field2, Date3 AS VisitDate
From YourTable
UNION ALL SELECT Field1, Field2, Date4 AS VisitDate
From YourTable
UNION ALL SELECT Field1, Field2, Date5 AS VisitDate
From YourTable;

--
KARL DEWEY
Build a little - Test a little


Jamesbfagan said:
I understand writing this in the row, but it would only be good for "one" of
the columns dates. We can go out to a house up to 5 times and sell something
In design view grid criteria row enter --
#1/1/2008# OR #2/3/2007# OR #5/9/2008#
In SQL --
[YourDate] = #1/1/2008# OR [YourDate] = #2/3/2007# OR [YourDate] =
#5/9/2008#
--
KARL DEWEY
Build a little - Test a little


:

We are an insurance company that has 5-10 sold dates per client. How in the
world do I (newbie) query on date 1, 2 3 etc?
Click to expand...
Click to expand...
Click to expand...
 
The field 1 and 2 and then visit date identifies the client/house/whatever
for the visit date.

It is my understanding your data is in the table like this --
Client Visit1 Visit2 Visit3 Visit4
J Brown 2/1/09 2/3/09 2/9/09
R Fox 1/5/09 1/15/09 2/5/09 2/11/09
S Ray 2/11/09

Your data for each visit should be in a separate record like this --
Client Visit
J Brown 2/1/09
J Brown 2/3/09
J Brown 2/9/09
R Fox 1/5/09
R Fox 1/15/09
R Fox 2/5/09
R Fox 2/11/09

--
KARL DEWEY
Build a little - Test a little


Jamesbfagan said:
Hi Karl, (I'm very new to this) would you mind breaking this down for me so a
kid can understand it? I think I'm going all over the map trying to figure
out what you mean by 1 record per visit and not column per visit. And I'm
not to sure what the field 1 and 2 and then visit date. Thank you for your
help.

KARL DEWEY said:
Yes for each column but on a different criteria row.
You should have a record for each visit, not a column per visit.
Use a union query to align your data the correct way ---
SELECT Field1, Field2, Date1 AS VisitDate
From YourTable
UNION ALL SELECT Field1, Field2, Date2 AS VisitDate
From YourTable
UNION ALL SELECT Field1, Field2, Date3 AS VisitDate
From YourTable
UNION ALL SELECT Field1, Field2, Date4 AS VisitDate
From YourTable
UNION ALL SELECT Field1, Field2, Date5 AS VisitDate
From YourTable;

--
KARL DEWEY
Build a little - Test a little


Jamesbfagan said:
I understand writing this in the row, but it would only be good for "one" of
the columns dates. We can go out to a house up to 5 times and sell something
on 5 different dates. So, I have 5 different columns, do I put this formula
under each of the date columns in the query? Thank you.

:

In design view grid criteria row enter --
#1/1/2008# OR #2/3/2007# OR #5/9/2008#
In SQL --
[YourDate] = #1/1/2008# OR [YourDate] = #2/3/2007# OR [YourDate] =
#5/9/2008#
--
KARL DEWEY
Build a little - Test a little


:

We are an insurance company that has 5-10 sold dates per client. How in the
world do I (newbie) query on date 1, 2 3 etc?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top