Can I do this in Access? Please help

  • Thread starter Thread starter caseysmydog
  • Start date Start date
SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

That should return all records with a date of death in the previous month.

Change the -1 to -3 to get date of death three months prior, -5 for five
months, etc.

Transferring that data into a stored form letter is a bit more complex.
Alfred Kallal has one example for doing this at his site. If you are
just beginning this may be difficult to use.
Word Merge Code

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html
 
SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

SELECT Table1.Deceased, Table1.[Date of Death], Table1.[Caregivers Name],
Table1.Address
FROM Table1
WHERE (((Table1.[Date of Death])="1")) OR (((Table1.[Date of
Death])=DateAdd("m",1,[DateOfDeath])))

Thank you for the above...I've tried both and they return an error:
Syntax In Subquery in this expression is incorrect.
Check the Subquery syntax & enclose Subquery in parenthesis.

I know I'm beginning to be a pain--I bought 2 books on Access today and will
study them. Any thoughts on these query's?
Thanks,
 
Are you running ONE query.

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

Open a new query
Do NOT ADD any tables, just close the dialog to select tables.
Switch to SQL view (View: SQL from the menu)
Paste the above into the SQL view of the query

Run the query. IF you get a syntax error then I am baffled, but post
back and let us know what the error is.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
It Worked...Thanks Will get back to you
--
David


John Spencer said:
Are you running ONE query.

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

Open a new query
Do NOT ADD any tables, just close the dialog to select tables.
Switch to SQL view (View: SQL from the menu)
Paste the above into the SQL view of the query

Run the query. IF you get a syntax error then I am baffled, but post
back and let us know what the error is.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

SELECT Table1.Deceased, Table1.[Date of Death], Table1.[Caregivers Name],
Table1.Address
FROM Table1
WHERE (((Table1.[Date of Death])="1")) OR (((Table1.[Date of
Death])=DateAdd("m",1,[DateOfDeath])))

Thank you for the above...I've tried both and they return an error:
Syntax In Subquery in this expression is incorrect.
Check the Subquery syntax & enclose Subquery in parenthesis.

I know I'm beginning to be a pain--I bought 2 books on Access today and will
study them. Any thoughts on these query's?
Thanks,
Click to expand...
Click to expand...
 
The query is working fine...trying to understand the relationship of the
number 1s in the query.
If I need to change the 1 to a 3(for 3 months) which to change?
Thanks again.
--
David


John Spencer said:
Are you running ONE query.

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

Open a new query
Do NOT ADD any tables, just close the dialog to select tables.
Switch to SQL view (View: SQL from the menu)
Paste the above into the SQL view of the query

Run the query. IF you get a syntax error then I am baffled, but post
back and let us know what the error is.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

SELECT Table1.Deceased, Table1.[Date of Death], Table1.[Caregivers Name],
Table1.Address
FROM Table1
WHERE (((Table1.[Date of Death])="1")) OR (((Table1.[Date of
Death])=DateAdd("m",1,[DateOfDeath])))

Thank you for the above...I've tried both and they return an error:
Syntax In Subquery in this expression is incorrect.
Check the Subquery syntax & enclose Subquery in parenthesis.

I know I'm beginning to be a pain--I bought 2 books on Access today and will
study them. Any thoughts on these query's?
Thanks,
Click to expand...
Click to expand...
 
For three months, you would need

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-3,1) AND
DateSerial(Year(Date()),Month(Date())-2,1-1)

For 6 months
SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-6,1) AND
DateSerial(Year(Date()),Month(Date())-5,1-1)

For 12 months
SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-12,1) AND
DateSerial(Year(Date()),Month(Date())-11,1-1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
John, this working very well except for the 12 month query.
For instance, we had 4 people die in Dec., '07.
Yet it pulls only 2.
The other two are lower day dates in Dec. than the two pulled.
For all the other months, the formula has pulled successfully and the day
dates do not affect, can be higher or lower.
Can you share your thoughts?
Many thanks
--
David


John Spencer said:
For three months, you would need

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-3,1) AND
DateSerial(Year(Date()),Month(Date())-2,1-1)

For 6 months
SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-6,1) AND
DateSerial(Year(Date()),Month(Date())-5,1-1)

For 12 months
SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-12,1) AND
DateSerial(Year(Date()),Month(Date())-11,1-1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The query is working fine...trying to understand the relationship of the
number 1s in the query.
If I need to change the 1 to a 3(for 3 months) which to change?
Thanks again.
Click to expand...
Click to expand...
 
The only think I can think of is to carefully check the date of death and make
sure the correct year has been entered. Make sure the date is 2007 and not
1907 or 2107 or ...

Also, make sure the month is December.

If you are storing Date of Death in a text field, Access may be attempting to
convert the string to dates for you. If so, it could be misunderstanding the
date string and converting 03/12/2007 to March 12, 2007 when you expect it to
see that as December 3, 2007.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top