Still stumped - can't get a date (range).

G

Guest

I have 2 dates - DOB (date of birth) and DOA (date of anniversary) for
contacts. I've got a union query that enables me to pull both of these fields
into a single field called CombinedDate. This is the SQL statement for the
union query:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;

I want to query those records where CombinedDate occurs within the next 2
weeks. I have 2 queries that work - one which gives me this info for DOB, and
one which gives me this info for DOA. The expression which gives me this info
for DOA is DateSerial(Year(Date()),Month([DOA]),Day([DOA])), and the
criteria for limiting the query returns to those occuring during the next 2
weeks is Between Date() And Date()+14. (Substitute DOB for DOA, and this
shows you the way the query is designed to give me this info for birthdays.)

What I really want to do is to have a query that pulls this info for both
DOB & DOA together. When I try to design the query using CombinedDate in
place of DOB or DOA, the query simply won't work. I get data mismatch errors,
wrong number of argument errors, and too few parentheses errors (depending on
which approach I try). The one thing I consistently get, in other words, is
errors. No results.

Help?

Thanks so much!!!
 
A

Amy Blankenship

Sue said:
I have 2 dates - DOB (date of birth) and DOA (date of anniversary) for
contacts. I've got a union query that enables me to pull both of these
fields
into a single field called CombinedDate. This is the SQL statement for the
union query:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;

I want to query those records where CombinedDate occurs within the next 2
weeks. I have 2 queries that work - one which gives me this info for DOB,
and
one which gives me this info for DOA. The expression which gives me this
info
for DOA is DateSerial(Year(Date()),Month([DOA]),Day([DOA])), and the
criteria for limiting the query returns to those occuring during the next
2
weeks is Between Date() And Date()+14. (Substitute DOB for DOA, and this
shows you the way the query is designed to give me this info for
birthdays.)
Click to expand...

Why not just put the where clause in each SELECT statement in the UNION
query?
 
G

Guest

If you don't want to follow Amy's suggestion, you should be able to do the
following. I have added where clauses to prevent NULL values from being
included.

SELECT LastName, FirstName, CardType, CombinedDate
FROM (
SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
WHERE tblContacts.DOB IS NOT NULL
UNION ALL
SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
WHERE tblContacts.DOA IS NOT NULL) as Cards
WHERE DateSerial(YEAR(DAte()), Month([CombinedDate]), Day([CombinedDate]))
BETWEEN Date() AND Date() + 14
ORDER BY Month([CombinedDate]), Day([CombinedDate])

HTH
Dale
 
G

Guest

What WHERE clause?
I'm clueless in Cincinnait here.

Can you help me figure out what to insert in this SQL statement (the union
query in question)?

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;

--
Thanks for your time!


Amy Blankenship said:
Sue said:
I have 2 dates - DOB (date of birth) and DOA (date of anniversary) for
contacts. I've got a union query that enables me to pull both of these
fields
into a single field called CombinedDate. This is the SQL statement for the
union query:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;

I want to query those records where CombinedDate occurs within the next 2
weeks. I have 2 queries that work - one which gives me this info for DOB,
and
one which gives me this info for DOA. The expression which gives me this
info
for DOA is DateSerial(Year(Date()),Month([DOA]),Day([DOA])), and the
criteria for limiting the query returns to those occuring during the next
2
weeks is Between Date() And Date()+14. (Substitute DOB for DOA, and this
shows you the way the query is designed to give me this info for
birthdays.)
Click to expand...

Why not just put the where clause in each SELECT statement in the UNION
query?
Click to expand...
 
G

Guest

I tred that - didn't work.
A friend of mine who's far more experienced w/Access than I suspects it may
be some sort of corruption in the database. If so, what's a girl to do???
--
Thanks for your time!


Dale Fye said:
If you don't want to follow Amy's suggestion, you should be able to do the
following. I have added where clauses to prevent NULL values from being
included.

SELECT LastName, FirstName, CardType, CombinedDate
FROM (
SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
WHERE tblContacts.DOB IS NOT NULL
UNION ALL
SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
WHERE tblContacts.DOA IS NOT NULL) as Cards
WHERE DateSerial(YEAR(DAte()), Month([CombinedDate]), Day([CombinedDate]))
BETWEEN Date() AND Date() + 14
ORDER BY Month([CombinedDate]), Day([CombinedDate])

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


Sue said:
I have 2 dates - DOB (date of birth) and DOA (date of anniversary) for
contacts. I've got a union query that enables me to pull both of these fields
into a single field called CombinedDate. This is the SQL statement for the
union query:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;

I want to query those records where CombinedDate occurs within the next 2
weeks. I have 2 queries that work - one which gives me this info for DOB, and
one which gives me this info for DOA. The expression which gives me this info
for DOA is DateSerial(Year(Date()),Month([DOA]),Day([DOA])), and the
criteria for limiting the query returns to those occuring during the next 2
weeks is Between Date() And Date()+14. (Substitute DOB for DOA, and this
shows you the way the query is designed to give me this info for birthdays.)

What I really want to do is to have a query that pulls this info for both
DOB & DOA together. When I try to design the query using CombinedDate in
place of DOB or DOA, the query simply won't work. I get data mismatch errors,
wrong number of argument errors, and too few parentheses errors (depending on
which approach I try). The one thing I consistently get, in other words, is
errors. No results.

Help?

Thanks so much!!!
Click to expand...
Click to expand...
 
G

Guest

Try this:

SELECT LastName, FirstName, "Birthday" As CardType,
DOB AS CombinedDate
FROM tblContacts
WHERE DATESERIAL(YEAR(DATE()),MONTH(DOB),DAY(DOB))
BETWEEN DATE() AND DATE()+14
UNION ALL
SELECT LastName, FirstName, "Anniversary", DOA
FROM tblContacts
WHERE DATESERIAL(YEAR(DATE()),MONTH(DOA),DAY(DOA))
BETWEEN DATE() AND DATE()+14
ORDER BY CombinedDate;

Fundamentally, however, your problem stems from a design flaw. By having
separate columns for DOB and DOA you are doing what's known as 'encoding data
as column headings'. This contravenes the 'information principal' which
requires that in a relational database data be stored only as values at
column positions in rows in tables. The table should really be decomposed
into two related tables, one for People and one for EventDates say. The
latter would have a foreign key column which references the primary key of
the people table, an EventDate column and a EventType column with possible
values such as Birthday, Anniversary etc. You can then simply join the
tables:

SELECT LastName, FirstName, EventType, EventDate
FROM People INNER JOIN EventDates
ON People.PersonID = EventDates.PersonID
WHERE DATESERIAL(YEAR(DATE()),MONTH(EventDate),DAY(EventDate))
BETWEEN DATE() AND DATE()+14
ORDER BY EventDate;

To complete the model you should really also have an EventTypes table with a
single column EventType with unique values Birthday, Anniversary etc. This
would be related to the EventDates table on EventType with referential
integrity enforced. This prevents invalid data being entered, by a typo for
instance, into the EventType column in EventDates.

For data entry a People form with an EventDates subform embedded in it would
be the appropriate approach.

Ken Sheridan
Stafford, England

Sue said:
I have 2 dates - DOB (date of birth) and DOA (date of anniversary) for
contacts. I've got a union query that enables me to pull both of these fields
into a single field called CombinedDate. This is the SQL statement for the
union query:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;

I want to query those records where CombinedDate occurs within the next 2
weeks. I have 2 queries that work - one which gives me this info for DOB, and
one which gives me this info for DOA. The expression which gives me this info
for DOA is DateSerial(Year(Date()),Month([DOA]),Day([DOA])), and the
criteria for limiting the query returns to those occuring during the next 2
weeks is Between Date() And Date()+14. (Substitute DOB for DOA, and this
shows you the way the query is designed to give me this info for birthdays.)

What I really want to do is to have a query that pulls this info for both
DOB & DOA together. When I try to design the query using CombinedDate in
place of DOB or DOA, the query simply won't work. I get data mismatch errors,
wrong number of argument errors, and too few parentheses errors (depending on
which approach I try). The one thing I consistently get, in other words, is
errors. No results.

Help?

Thanks so much!!!
Click to expand...
 
G

Guest

SInce the DOB & DOA fields are unique to an individual, I hadn't considered
that it might be necessary to split them into 1 (ore even 2) addiitional
tables as your post suggests.
--
Thanks for your time!


Ken Sheridan said:
Try this:

SELECT LastName, FirstName, "Birthday" As CardType,
DOB AS CombinedDate
FROM tblContacts
WHERE DATESERIAL(YEAR(DATE()),MONTH(DOB),DAY(DOB))
BETWEEN DATE() AND DATE()+14
UNION ALL
SELECT LastName, FirstName, "Anniversary", DOA
FROM tblContacts
WHERE DATESERIAL(YEAR(DATE()),MONTH(DOA),DAY(DOA))
BETWEEN DATE() AND DATE()+14
ORDER BY CombinedDate;

Fundamentally, however, your problem stems from a design flaw. By having
separate columns for DOB and DOA you are doing what's known as 'encoding data
as column headings'. This contravenes the 'information principal' which
requires that in a relational database data be stored only as values at
column positions in rows in tables. The table should really be decomposed
into two related tables, one for People and one for EventDates say. The
latter would have a foreign key column which references the primary key of
the people table, an EventDate column and a EventType column with possible
values such as Birthday, Anniversary etc. You can then simply join the
tables:

SELECT LastName, FirstName, EventType, EventDate
FROM People INNER JOIN EventDates
ON People.PersonID = EventDates.PersonID
WHERE DATESERIAL(YEAR(DATE()),MONTH(EventDate),DAY(EventDate))
BETWEEN DATE() AND DATE()+14
ORDER BY EventDate;

To complete the model you should really also have an EventTypes table with a
single column EventType with unique values Birthday, Anniversary etc. This
would be related to the EventDates table on EventType with referential
integrity enforced. This prevents invalid data being entered, by a typo for
instance, into the EventType column in EventDates.

For data entry a People form with an EventDates subform embedded in it would
be the appropriate approach.

Ken Sheridan
Stafford, England

Sue said:
I have 2 dates - DOB (date of birth) and DOA (date of anniversary) for
contacts. I've got a union query that enables me to pull both of these fields
into a single field called CombinedDate. This is the SQL statement for the
union query:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;

I want to query those records where CombinedDate occurs within the next 2
weeks. I have 2 queries that work - one which gives me this info for DOB, and
one which gives me this info for DOA. The expression which gives me this info
for DOA is DateSerial(Year(Date()),Month([DOA]),Day([DOA])), and the
criteria for limiting the query returns to those occuring during the next 2
weeks is Between Date() And Date()+14. (Substitute DOB for DOA, and this
shows you the way the query is designed to give me this info for birthdays.)

What I really want to do is to have a query that pulls this info for both
DOB & DOA together. When I try to design the query using CombinedDate in
place of DOB or DOA, the query simply won't work. I get data mismatch errors,
wrong number of argument errors, and too few parentheses errors (depending on
which approach I try). The one thing I consistently get, in other words, is
errors. No results.

Help?

Thanks so much!!!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Stumped with an upcoming birthdays query 16
I want a date (range). 4
I'd sure love to be able to do this. 1
Query date range 4
UNION query taking 5 minutes for approx 2000 records - any tune uptips? 1
Current Date in Query 12
round a date 6
Help writing Query 2

Top