A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Can I do this in Access? Please help



 
 
Thread Tools Display Modes
  #21  
Old November 15th, 2008, 09:19 AM posted to microsoft.public.access.gettingstarted
caseysmydog
external usenet poster
 
Posts: 17
Default Can I do this in Access? Please help


--
David


"John W. Vinson" wrote:

On Fri, 14 Nov 2008 08:48:13 -0800, caseysmydog
wrote:

Query:

Field Deceased Date of Death Caregivers Name Address
Table Table1 Table1 Table1
Table1
Sort
Show Y Y Y
Y
Criteria =1
or:
Dateadd("m",1)date of death))

Data above is skewed but it gives the idea.
Many thanks


If you want to display a query here, please open it in design view; select
View... SQL from the menu; and post the SQL text here. It may look cryptic but
it's the *real* query, for which the grid is just a design tool, and the folks
who answer questions here can read it like today's newspaper.

As written - if I'm interpreting it correctly - this query will return an
error because the expression Dateadd("m",1)date of death)) makes no sense.
Even if it were corrected to Dateadd("m",1, [date of death]) it would return
only those records where the Date of Death was December 31, 1899 (one day
after the "anchor point" for Date/TIme fields), or was equal to one month
after the date of death - so you'll get no results.

Perhaps you could explain in words what results you want from the query.
--

John W. Vinson [MVP]

  #22  
Old November 15th, 2008, 01:22 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 2,364
Default Can I do this in Access? Please help

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/AlbertKal.../msaccess.html
  #23  
Old November 15th, 2008, 10:48 PM posted to microsoft.public.access.gettingstarted
caseysmydog
external usenet poster
 
Posts: 17
Default Can I do this in Access? Please help

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,
--
David


"John Spencer" wrote:

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/AlbertKal.../msaccess.html

  #24  
Old November 15th, 2008, 11:01 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 2,364
Default Can I do this in Access? Please help

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
'================================================= ===


caseysmydog wrote:
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,

  #25  
Old November 16th, 2008, 02:27 AM posted to microsoft.public.access.gettingstarted
caseysmydog
external usenet poster
 
Posts: 17
Default Can I do this in Access? Please help

It Worked...Thanks Will get back to you
--
David


"John Spencer" wrote:

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
'================================================= ===


caseysmydog wrote:
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,


  #26  
Old November 17th, 2008, 10:53 PM posted to microsoft.public.access.gettingstarted
caseysmydog
external usenet poster
 
Posts: 17
Default Can I do this in Access? Please help

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" wrote:

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
'================================================= ===


caseysmydog wrote:
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,


  #27  
Old November 18th, 2008, 12:57 AM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 2,364
Default Can I do this in Access? Please help

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
'================================================= ===


caseysmydog wrote:
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.

  #28  
Old December 15th, 2008, 11:16 PM posted to microsoft.public.access.gettingstarted
caseysmydog
external usenet poster
 
Posts: 17
Default Can I do this in Access? Please help

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" wrote:

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
'================================================= ===


caseysmydog wrote:
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.


  #29  
Old December 16th, 2008, 01:31 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Can I do this in Access? Please help

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

caseysmydog wrote:
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.