[mso] Re: Date in Access
- From: Glenda Wells <gwells@xxxxxxxxxxx>
- To: "'mso@xxxxxxxxxxxxx'" <mso@xxxxxxxxxxxxx>
- Date: Mon, 10 Feb 2003 13:06:15 -0500
YIPPEE...twirling in my chair giggling hysterically!
I guessed at the 3D and the 20 being not part of the equation but since I'm
not all that familiar with writing code or SQL, I had to ask. Sorry Linda I
don't remember seeing that in the discussions...considering all that's
happening on the lists and my routine workday, I'm surprised I remember my
own name.
Anyway, I wrote in SQL mode (copied actually) your SELECT statement Anthony
and removed the oddities but still got a syntax error. It didn't work until
I removed the sub-query from SQL and typed it directly in the criteria row.
The end result in SQL works beautifully. Next I copied the statement into
all 26 of the queries then ran the macro which runs the whole mess and got
the desired results for my report.
I've already created a parameter query to prompt for date1 and date2.
Just to let y'all know what I'm doing...it's a report that has to run at
least weekly for a 10:00 am meeting, but also may be run at any time daily
after that. When I inherited this project, the process took in excess of 4
hours and the resulting values were questionable.
With the help of this list and my own insistence that if it works once, it
should work every time, I've managed to get the process down to less than a
minute. For us, that's HUGE. Still, the whole thing is wrong. The 26
queries are make-tables and we have to manually type the results into an
Excel spreadsheet. My next "phase" of this project is to find a way to more
automate that part of the process...i.e. eliminate as much human involvement
as possible. We must use Excel because there are several columns that
change weekly but are from a source over which I have absolutely no control.
Also, the data is ultimately shared with dozens of persons who don't have
Access.
hugs, cartwheels, cheering and MANY THANKS for the help.
SELECT SATURN_SRBRECR.SRBRECR_PIDM, SATURN_SRBRECR.SRBRECR_STYP_CODE,
SATURN_SRBRECR.SRBRECR_TERM_CODE INTO tblTestingDate
FROM SATURN_SRBRECR
WHERE (((SATURN_SRBRECR.SRBRECR_ADD_DATE)<(select [date1] from tblDate)) AND
((SATURN_SRBRECR.SRBRECR_LEVL_CODE)="UG"))
GROUP BY SATURN_SRBRECR.SRBRECR_PIDM, SATURN_SRBRECR.SRBRECR_STYP_CODE,
SATURN_SRBRECR.SRBRECR_TERM_CODE
HAVING (((SATURN_SRBRECR.SRBRECR_TERM_CODE)="200315") AND
((SATURN_SRBRECR.SRBRECR_STYP_CODE)="N" Or
(SATURN_SRBRECR.SRBRECR_STYP_CODE)="T"));
-----Original Message-----
From: Colli, Anthony G [mailto:Anthony.Colli@xxxxxxx]
Sent: Monday, February 10, 2003 10:52 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Date in Access
g-
(SELECT from tblDate, where [date1] <=3D3D [SRBRECR_ADD_DATE])=3D20
if [SRBRECR_ADD_DATE] is not in tblDate then an error will be =3D =
generated, it's not an available column.=3D20
Will this work?
SELECT SATURN_SRBRECR.SRBRECR_PIDM, SATURN_SRBRECR.SRBRECR_STYP_CODE,
SATURN_SRBRECR.SRBRECR_TERM_CODE INTO tblInq200315 FROM SATURN_SRBRECR =
WHERE (((SATURN_SRBRECR.SRBRECR_ADD_DATE)<=3D3D(SELECT [date1] FROM =3D
tblDate) AND
((SATURN_SRBRECR.SRBRECR_LEVL_CODE)=3D3D"UG"))
GROUP BY SATURN_SRBRECR.SRBRECR_PIDM, SATURN_SRBRECR.SRBRECR_STYP_CODE,
SATURN_SRBRECR.SRBRECR_TERM_CODE HAVING
(((SATURN_SRBRECR.SRBRECR_STYP_CODE)=3D3D"N" Or
(SATURN_SRBRECR.SRBRECR_STYP_CODE)=3D3D"T") AND
((SATURN_SRBRECR.SRBRECR_TERM_CODE)=3D3D"200315"));
-Anthony
-----Original Message-----
From: Glenda Wells [mailto:gwells@xxxxxxxxxxx]
Sent: Monday, February 10, 2003 8:30 AM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Date in Access
Hi Anthony.
I'm trying...really. I placed this in the criteria row for the date =3D =
field I need and get a syntax error.
(SELECT from tblDate, where [date1] <=3D3D [SRBRECR_ADD_DATE])
tblDate is the table I made to contain the dates. It has 1 row with 2
columns for date1 and date2. SRBRECR_ADD_DATE is the field in the linked
table that I need my date to =3D be less or equal to.
The query has 5 fields, ID, Type, Term, Date, Level. All of which =3D =
(except
ID) have criteria.
Without my attempt to "automate" the date, the SQL looks like this:
SELECT SATURN_SRBRECR.SRBRECR_PIDM, SATURN_SRBRECR.SRBRECR_STYP_CODE,
SATURN_SRBRECR.SRBRECR_TERM_CODE INTO tblInq200315 FROM SATURN_SRBRECR =
WHERE
(((SATURN_SRBRECR.SRBRECR_ADD_DATE)<=3D3D#2/11/2002#) AND
((SATURN_SRBRECR.SRBRECR_LEVL_CODE)=3D3D"UG"))
GROUP BY SATURN_SRBRECR.SRBRECR_PIDM, SATURN_SRBRECR.SRBRECR_STYP_CODE,
SATURN_SRBRECR.SRBRECR_TERM_CODE HAVING
(((SATURN_SRBRECR.SRBRECR_STYP_CODE)=3D3D"N" Or
(SATURN_SRBRECR.SRBRECR_STYP_CODE)=3D3D"T") AND
((SATURN_SRBRECR.SRBRECR_TERM_CODE)=3D3D"200315"));
In order for me to get the data I need, 26 queries, requiring dates must =
=3D be run (half for date1 and half for date2) and manually typing in the =
date =3D all 26 times, several times a day is getting old, not to mention
the = number =3D of times I typo and have to start over!!!
I think this must be a sub-query but...HELPPPP!
g
************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx = or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to=20
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the =
quotes)
in the subject line.
Or, visit the group's homepage and use the dropdown menu. This will = also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, = send
a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an =
invitation with instructions. Once you are a member of the files group, you
can go here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in
the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
