Querying on multiple date fields

  • 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 these dates? Product 1 sold Nov 15th, Product 2
sold Dec 15th, Product 3 sold Jan 15th. How would I query just the January
15th sale (which could be in sold date 3 or 2 or even 10)?
 
Something along these lines in the Where condition of the query:

"WHERE [datefield] = #" & datevariable & "#"

Assuming that datevariable is a variable of the Date datatype that holds
your Jan 15th date


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain
 
I'm a moron, don't pay attention to me.

Apparently I'm still not in the habit of fully understanding questions
before I give so-called answers.

I think I'll refrain from offering 'advice' for a while until I can get this
one nailed down.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


dymondjack said:
Something along these lines in the Where condition of the query:

"WHERE [datefield] = #" & datevariable & "#"

Assuming that datevariable is a variable of the Date datatype that holds
your Jan 15th date


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


Jamesbfagan said:
We are an insurance company that has 5-10 sold dates per client. How in the
world do I (newbie) query on these dates? Product 1 sold Nov 15th, Product 2
sold Dec 15th, Product 3 sold Jan 15th. How would I query just the January
15th sale (which could be in sold date 3 or 2 or even 10)?
Click to expand...
Click to expand...
 
I'll try this again....


If these are all fields in the same table, you have a data normalization
issue. Generally, each of these Sold Dates would be in a related table on
the 'many' side of the relationship.

That said, if you have 10 date fields in the same table, and you need to
find a record that has Jan 15th in any one of those 10 date fields, this is
what you need to do.


"SELECT * FROM tblname WHERE " & _
"[ID] = " & IDVariable & " And " & _
"(([datefield1] = #" & DateVariable & "#) Or " & _
"([datefield2] = #" & DateVariable & "#) Or " & _
"([datefield3] = #" & DateVariable & "#) Or " & _
"([datefield4] = #" & DateVariable & "#) Or " & _
"([datefield5] = #" & DateVariable & "#) Or " & _
"([datefield6] = #" & DateVariable & "#) Or " & _
"([datefield7] = #" & DateVariable & "#) Or " & _
"([datefield8] = #" & DateVariable & "#) Or " & _
"([datefield9] = #" & DateVariable & "#) Or " & _
"([datefield10] = #" & DateVariable & "#))"


This gets the first ID found (for the client), and assumes that DateVariable
is a variable of the Data datatype that holds the date you are trying to
find. It checks each of the date fields, and if one of them holds the same
data, adds that record to the SQL. You *should* wind up with one record, or
none, when its done.

As you can see, going through each record and running 10 checks for each
record is going to take a little while. Much much faster to use a related
table.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain
 
Jack, I did build all the dates on the MAIN table-in fact almost ALL my
fields are built in the MAIN table. The below is way over my head. What
would you suggest as far as how to make this easier? Thank you.

dymondjack said:
I'll try this again....


If these are all fields in the same table, you have a data normalization
issue. Generally, each of these Sold Dates would be in a related table on
the 'many' side of the relationship.

That said, if you have 10 date fields in the same table, and you need to
find a record that has Jan 15th in any one of those 10 date fields, this is
what you need to do.


"SELECT * FROM tblname WHERE " & _
"[ID] = " & IDVariable & " And " & _
"(([datefield1] = #" & DateVariable & "#) Or " & _
"([datefield2] = #" & DateVariable & "#) Or " & _
"([datefield3] = #" & DateVariable & "#) Or " & _
"([datefield4] = #" & DateVariable & "#) Or " & _
"([datefield5] = #" & DateVariable & "#) Or " & _
"([datefield6] = #" & DateVariable & "#) Or " & _
"([datefield7] = #" & DateVariable & "#) Or " & _
"([datefield8] = #" & DateVariable & "#) Or " & _
"([datefield9] = #" & DateVariable & "#) Or " & _
"([datefield10] = #" & DateVariable & "#))"


This gets the first ID found (for the client), and assumes that DateVariable
is a variable of the Data datatype that holds the date you are trying to
find. It checks each of the date fields, and if one of them holds the same
data, adds that record to the SQL. You *should* wind up with one record, or
none, when its done.

As you can see, going through each record and running 10 checks for each
record is going to take a little while. Much much faster to use a related
table.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


Jamesbfagan said:
We are an insurance company that has 5-10 sold dates per client. How in the
world do I (newbie) query on these dates? Product 1 sold Nov 15th, Product 2
sold Dec 15th, Product 3 sold Jan 15th. How would I query just the January
15th sale (which could be in sold date 3 or 2 or even 10)?
Click to expand...
Click to expand...
 
I did build all the dates on the MAIN table-in fact almost ALL my
fields are built in the MAIN table. <clip> What
would you suggest as far as how to make this easier?
Click to expand...

Get your data into a correctly normalized structure. You should never put
fields into a table based on how many times you *think* something might
happen (ex. I *think* I'm not going to wind up with more than 10 sold dates).

Instead, have one table for your clients, that holds information specific
only to the client. Then, a table for Sold dates, specific only to the sold
dates.

tblClients would consist of:
fldCliendID (Primary Key, Autonumber)
fldName
... (infromation specific to the client)

tblSoldDates would consist of:
fldSellID (Primary Key, Autonumber)
fldClientID (Foreign Key, Long Integer)
fldSoldDate (date)
... (information specific to each instance of a sell)

Create a One-To-Many relationship between tblClients and tblSoldDates

That is the correct way to do it. Then, your SQL string would be:

"SELECT * FROM tblSoldDates WHERE [fldClientID] = " & lngID _
& " AND [fldSoldDate] = #" datevariable & "#"


Check out these two sites for information regarding the basics on setting up
a normalized database:

http://www.accessmvp.com/strive4peace/

http://www.allenbrowne.com/tips.html

(these will explain basic SQL as well, so you will have a better
understanding of the examples I've provided...)


Changing your tables around might seem a like a nightmare at first, but it
will be far far far less of a nightmare than the way you are trying to do it
now. Databases applications such as Access are designed to be used with
these types of structures, and trying to do it any other way is generally a
disaster waiting to happen, with many many headaches before you reach the
dead end and realize you have to start over from scratch anyway.

Good luck!


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


Jamesbfagan said:
Jack, I did build all the dates on the MAIN table-in fact almost ALL my
fields are built in the MAIN table. The below is way over my head. What
would you suggest as far as how to make this easier? Thank you.

dymondjack said:
I'll try this again....


If these are all fields in the same table, you have a data normalization
issue. Generally, each of these Sold Dates would be in a related table on
the 'many' side of the relationship.

That said, if you have 10 date fields in the same table, and you need to
find a record that has Jan 15th in any one of those 10 date fields, this is
what you need to do.


"SELECT * FROM tblname WHERE " & _
"[ID] = " & IDVariable & " And " & _
"(([datefield1] = #" & DateVariable & "#) Or " & _
"([datefield2] = #" & DateVariable & "#) Or " & _
"([datefield3] = #" & DateVariable & "#) Or " & _
"([datefield4] = #" & DateVariable & "#) Or " & _
"([datefield5] = #" & DateVariable & "#) Or " & _
"([datefield6] = #" & DateVariable & "#) Or " & _
"([datefield7] = #" & DateVariable & "#) Or " & _
"([datefield8] = #" & DateVariable & "#) Or " & _
"([datefield9] = #" & DateVariable & "#) Or " & _
"([datefield10] = #" & DateVariable & "#))"


This gets the first ID found (for the client), and assumes that DateVariable
is a variable of the Data datatype that holds the date you are trying to
find. It checks each of the date fields, and if one of them holds the same
data, adds that record to the SQL. You *should* wind up with one record, or
none, when its done.

As you can see, going through each record and running 10 checks for each
record is going to take a little while. Much much faster to use a related
table.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


Jamesbfagan said:
We are an insurance company that has 5-10 sold dates per client. How in the
world do I (newbie) query on these dates? Product 1 sold Nov 15th, Product 2
sold Dec 15th, Product 3 sold Jan 15th. How would I query just the January
15th sale (which could be in sold date 3 or 2 or even 10)?
Click to expand...
Click to expand...
Click to expand...
 
Thank you Jack for all of your help! I will go to the websites to start over.
You have been great! Thank you again!

dymondjack said:
I did build all the dates on the MAIN table-in fact almost ALL my
fields are built in the MAIN table. <clip> What
would you suggest as far as how to make this easier?
Click to expand...

Get your data into a correctly normalized structure. You should never put
fields into a table based on how many times you *think* something might
happen (ex. I *think* I'm not going to wind up with more than 10 sold dates).

Instead, have one table for your clients, that holds information specific
only to the client. Then, a table for Sold dates, specific only to the sold
dates.

tblClients would consist of:
fldCliendID (Primary Key, Autonumber)
fldName
... (infromation specific to the client)

tblSoldDates would consist of:
fldSellID (Primary Key, Autonumber)
fldClientID (Foreign Key, Long Integer)
fldSoldDate (date)
... (information specific to each instance of a sell)

Create a One-To-Many relationship between tblClients and tblSoldDates

That is the correct way to do it. Then, your SQL string would be:

"SELECT * FROM tblSoldDates WHERE [fldClientID] = " & lngID _
& " AND [fldSoldDate] = #" datevariable & "#"


Check out these two sites for information regarding the basics on setting up
a normalized database:

http://www.accessmvp.com/strive4peace/

http://www.allenbrowne.com/tips.html

(these will explain basic SQL as well, so you will have a better
understanding of the examples I've provided...)


Changing your tables around might seem a like a nightmare at first, but it
will be far far far less of a nightmare than the way you are trying to do it
now. Databases applications such as Access are designed to be used with
these types of structures, and trying to do it any other way is generally a
disaster waiting to happen, with many many headaches before you reach the
dead end and realize you have to start over from scratch anyway.

Good luck!


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


Jamesbfagan said:
Jack, I did build all the dates on the MAIN table-in fact almost ALL my
fields are built in the MAIN table. The below is way over my head. What
would you suggest as far as how to make this easier? Thank you.

dymondjack said:
I'll try this again....


If these are all fields in the same table, you have a data normalization
issue. Generally, each of these Sold Dates would be in a related table on
the 'many' side of the relationship.

That said, if you have 10 date fields in the same table, and you need to
find a record that has Jan 15th in any one of those 10 date fields, this is
what you need to do.


"SELECT * FROM tblname WHERE " & _
"[ID] = " & IDVariable & " And " & _
"(([datefield1] = #" & DateVariable & "#) Or " & _
"([datefield2] = #" & DateVariable & "#) Or " & _
"([datefield3] = #" & DateVariable & "#) Or " & _
"([datefield4] = #" & DateVariable & "#) Or " & _
"([datefield5] = #" & DateVariable & "#) Or " & _
"([datefield6] = #" & DateVariable & "#) Or " & _
"([datefield7] = #" & DateVariable & "#) Or " & _
"([datefield8] = #" & DateVariable & "#) Or " & _
"([datefield9] = #" & DateVariable & "#) Or " & _
"([datefield10] = #" & DateVariable & "#))"


This gets the first ID found (for the client), and assumes that DateVariable
is a variable of the Data datatype that holds the date you are trying to
find. It checks each of the date fields, and if one of them holds the same
data, adds that record to the SQL. You *should* wind up with one record, or
none, when its done.

As you can see, going through each record and running 10 checks for each
record is going to take a little while. Much much faster to use a related
table.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

We are an insurance company that has 5-10 sold dates per client. How in the
world do I (newbie) query on these dates? Product 1 sold Nov 15th, Product 2
sold Dec 15th, Product 3 sold Jan 15th. How would I query just the January
15th sale (which could be in sold date 3 or 2 or even 10)?
Click to expand...
Click to expand...
Click to expand...
 
Hi Jack, I went back and made up the tables as needed and have a couple
questions:

1. You stated I needed another table for dates etc.

tblSoldDates would consist of:

2. I made a product table and how do I tie that in to the dates table. Along
with the client since we can go back a few times and sell a policy (product)
Thank you Jack for all of your help! I will go to the websites to start over.
You have been great! Thank you again!

dymondjack said:
I did build all the dates on the MAIN table-in fact almost ALL my
fields are built in the MAIN table. <clip> What
would you suggest as far as how to make this easier?
Click to expand...

Get your data into a correctly normalized structure. You should never put
fields into a table based on how many times you *think* something might
happen (ex. I *think* I'm not going to wind up with more than 10 sold dates).

Instead, have one table for your clients, that holds information specific
only to the client. Then, a table for Sold dates, specific only to the sold
dates.

tblClients would consist of:
fldCliendID (Primary Key, Autonumber)
fldName
... (infromation specific to the client)

tblSoldDates would consist of:
fldSellID (Primary Key, Autonumber)
fldClientID (Foreign Key, Long Integer)
fldSoldDate (date)
... (information specific to each instance of a sell)

Create a One-To-Many relationship between tblClients and tblSoldDates

That is the correct way to do it. Then, your SQL string would be:

"SELECT * FROM tblSoldDates WHERE [fldClientID] = " & lngID _
& " AND [fldSoldDate] = #" datevariable & "#"


Check out these two sites for information regarding the basics on setting up
a normalized database:

http://www.accessmvp.com/strive4peace/

http://www.allenbrowne.com/tips.html

(these will explain basic SQL as well, so you will have a better
understanding of the examples I've provided...)


Changing your tables around might seem a like a nightmare at first, but it
will be far far far less of a nightmare than the way you are trying to do it
now. Databases applications such as Access are designed to be used with
these types of structures, and trying to do it any other way is generally a
disaster waiting to happen, with many many headaches before you reach the
dead end and realize you have to start over from scratch anyway.

Good luck!


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


Jamesbfagan said:
Jack, I did build all the dates on the MAIN table-in fact almost ALL my
fields are built in the MAIN table. The below is way over my head. What
would you suggest as far as how to make this easier? Thank you.

:

I'll try this again....


If these are all fields in the same table, you have a data normalization
issue. Generally, each of these Sold Dates would be in a related table on
the 'many' side of the relationship.

That said, if you have 10 date fields in the same table, and you need to
find a record that has Jan 15th in any one of those 10 date fields, this is
what you need to do.


"SELECT * FROM tblname WHERE " & _
"[ID] = " & IDVariable & " And " & _
"(([datefield1] = #" & DateVariable & "#) Or " & _
"([datefield2] = #" & DateVariable & "#) Or " & _
"([datefield3] = #" & DateVariable & "#) Or " & _
"([datefield4] = #" & DateVariable & "#) Or " & _
"([datefield5] = #" & DateVariable & "#) Or " & _
"([datefield6] = #" & DateVariable & "#) Or " & _
"([datefield7] = #" & DateVariable & "#) Or " & _
"([datefield8] = #" & DateVariable & "#) Or " & _
"([datefield9] = #" & DateVariable & "#) Or " & _
"([datefield10] = #" & DateVariable & "#))"


This gets the first ID found (for the client), and assumes that DateVariable
is a variable of the Data datatype that holds the date you are trying to
find. It checks each of the date fields, and if one of them holds the same
data, adds that record to the SQL. You *should* wind up with one record, or
none, when its done.

As you can see, going through each record and running 10 checks for each
record is going to take a little while. Much much faster to use a related
table.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

We are an insurance company that has 5-10 sold dates per client. How in the
world do I (newbie) query on these dates? Product 1 sold Nov 15th, Product 2
sold Dec 15th, Product 3 sold Jan 15th. How would I query just the January
15th sale (which could be in sold date 3 or 2 or even 10)?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top