Finding Dates

  • Thread starter Thread starter Sara
  • Start date Start date
S

Sara

I receive data from an outside source - it's in .csv, and
the fields are:
SALEDATE, StoreNum, ASSOC#, ASSOC NAME and OVER/-SHORT.
We need to look at this data and find associates
(cashiers) whose drawers are off.

Loss Prevention (LP) needs the information by our Fiscal
Quarter. Our years run Feb through Jan. I have
a "MapDate" table with Start and End Dates for each week,
week # of year, Fiscal Month Num, FiscalQtr Num and Fiscal
Year Num.

I am trying to figure out how to get the RANGE of dates to
pull off of the Associate File to give LP the data they
need based on the fiscal calendar. I need to total by
store by week and month and quarter, and also (in separate
queries/reports) find over/short amounts outside of limits
and [more than 3 out of limits in rolling 90 day period].

Right now, I am trying to figure out how to set it up so a
user can "request" a report and it will know how to pull
the right dates from the file we receive. Do I have to
add "SaleDate" to my MapDate table to accomplish this or
is there some sort of function I can use?

Thanks,
Sara
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Two ways:

1. Use a subquery
2. Use the Access function DatePart()

1. Use a subquery:
===================

SELECT ...
FROM ...
WHERE SaleDate In
(SELECT MapDate FROM MapDates WHERE SalesQtr = 3)

This assumes that the MapDates table has all dates that will be in the
imported data. You'll have to change the subquery's WHERE clause
depending on the calander period (week, month, qtr, etc.).

See the Access Help article on the In operator.

To get the over/-short > 3 times in a time-period, it'd be something
like this:

SELECT ASSOC#
FROM ...
WHERE SaleDate In
(SELECT MapDate FROM MapDates WHERE SalesQtr = 3)
AND Over/-Short = True
GROUP BY Assoc#
HAVING COUNT(*) > 3

The sales' period is controlled by the subquery WHERE clause (as
described above). The HAVING clause will cause the result set to
return only Assoc#s that are greater than (>) three True, Over/
Shorts.


2. Use the Access function DatePart():
======================================

Instead of having a table of dates that correspond to your company's
fiscal calendar you may wish to use the Access function DatePart() and
modify it's result so it corresponds to your fiscal calendar. E.g.:

DatePart("q", #1/1/04#) = 1 (1st quarter in 2004)

If your co's 1st fiscal quarter starts Feb 1 you could modify the
DatePart() function like this (just add one month to the query date).
The query date is #2/1/03#.

1 = DatePart("Q", DateAdd("m", -1, #2/1/03#))

Jan 1 is Quarter 4:

4 = DatePart("Q", DateAdd("m", -1, #12/1/03#))

DatePart() can also return the week of the year and the month. All
you have to do is add the month offset to the query date. Using math
is usually easier than using a table.

Then the WHERE clause of your query would be simplified like this
(current quarter data):

....
WHERE SaleDate = DatePart("q", DateAdd("m" -1, Date())


HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCyCBIechKqOuFEgEQI5yACg0uZJhKHKUQ5sUHFLni1jZnaYNJYAoJMD
5mRhjRbcwTUCIt5P6w6GThP0
=Skw8
-----END PGP SIGNATURE-----

I receive data from an outside source - it's in .csv, and
the fields are:
SALEDATE, StoreNum, ASSOC#, ASSOC NAME and OVER/-SHORT.
We need to look at this data and find associates
(cashiers) whose drawers are off.

Loss Prevention (LP) needs the information by our Fiscal
Quarter. Our years run Feb through Jan. I have
a "MapDate" table with Start and End Dates for each week,
week # of year, Fiscal Month Num, FiscalQtr Num and Fiscal
Year Num.

I am trying to figure out how to get the RANGE of dates to
pull off of the Associate File to give LP the data they
need based on the fiscal calendar. I need to total by
store by week and month and quarter, and also (in separate
queries/reports) find over/short amounts outside of limits
and [more than 3 out of limits in rolling 90 day period].

Right now, I am trying to figure out how to set it up so a
user can "request" a report and it will know how to pull
the right dates from the file we receive. Do I have to
add "SaleDate" to my MapDate table to accomplish this or
is there some sort of function I can use?
Click to expand...
 
Thanks. I am still not there, I'm afraid to say. It looks
like I'm going to be best off to put all the individual
dates in the MapDates table. MapDates has only
WeekStarting and WeekEnding dates, and the data I have is
on each day of the week individually. Also, for the
Associate counting 3, I can try the concept, but since
it's a rolling 90 days, I think having the date in the
MapDate table and doing a date function that will subtract
90 days from "today" (or a date the user enters) would be
best.

Am I right?

I'm sorry to be so difficult with this; it's really a
tough one for me and I'm reading all the help associated
with your suggestions, but it still isn't there. That's
why I'm thinking a table with every day in it might be the
best?

thanks

-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Two ways:

1. Use a subquery
2. Use the Access function DatePart()

1. Use a subquery:
===================

SELECT ...
FROM ...
WHERE SaleDate In
(SELECT MapDate FROM MapDates WHERE SalesQtr = 3)

This assumes that the MapDates table has all dates that will be in the
imported data. You'll have to change the subquery's WHERE clause
depending on the calander period (week, month, qtr, etc.).

See the Access Help article on the In operator.

To get the over/-short > 3 times in a time-period, it'd be something
like this:

SELECT ASSOC#
FROM ...
WHERE SaleDate In
(SELECT MapDate FROM MapDates WHERE SalesQtr = 3)
AND Over/-Short = True
GROUP BY Assoc#
HAVING COUNT(*) > 3

The sales' period is controlled by the subquery WHERE clause (as
described above). The HAVING clause will cause the result set to
return only Assoc#s that are greater than (>) three True, Over/
Shorts.


2. Use the Access function DatePart():
======================================

Instead of having a table of dates that correspond to your company's
fiscal calendar you may wish to use the Access function DatePart() and
modify it's result so it corresponds to your fiscal calendar. E.g.:

DatePart("q", #1/1/04#) = 1 (1st quarter in 2004)

If your co's 1st fiscal quarter starts Feb 1 you could modify the
DatePart() function like this (just add one month to the query date).
The query date is #2/1/03#.

1 = DatePart("Q", DateAdd("m", -1, #2/1/03#))

Jan 1 is Quarter 4:

4 = DatePart("Q", DateAdd("m", -1, #12/1/03#))

DatePart() can also return the week of the year and the month. All
you have to do is add the month offset to the query date. Using math
is usually easier than using a table.

Then the WHERE clause of your query would be simplified like this
(current quarter data):

....
WHERE SaleDate = DatePart("q", DateAdd("m" -1, Date())


HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCyCBIechKqOuFEgEQI5yACg0uZJhKHKUQ5sUHFLni1jZnaYNJ YAoJMD
5mRhjRbcwTUCIt5P6w6GThP0
=Skw8
-----END PGP SIGNATURE-----

I receive data from an outside source - it's in .csv, and
the fields are:
SALEDATE, StoreNum, ASSOC#, ASSOC NAME and OVER/-SHORT.
We need to look at this data and find associates
(cashiers) whose drawers are off.

Loss Prevention (LP) needs the information by our Fiscal
Quarter. Our years run Feb through Jan. I have
a "MapDate" table with Start and End Dates for each week,
week # of year, Fiscal Month Num, FiscalQtr Num and Fiscal
Year Num.

I am trying to figure out how to get the RANGE of dates to
pull off of the Associate File to give LP the data they
need based on the fiscal calendar. I need to total by
store by week and month and quarter, and also (in separate
queries/reports) find over/short amounts outside of limits
and [more than 3 out of limits in rolling 90 day period].

Right now, I am trying to figure out how to set it up so a
user can "request" a report and it will know how to pull
the right dates from the file we receive. Do I have to
add "SaleDate" to my MapDate table to accomplish this or
is there some sort of function I can use?
Click to expand...

.
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

For the Over/-Short count > 3 times in 90 days you'd use the WHERE
clause like so:

PARAMETERS [Interval] Byte;
SELECT Assoc#
FROM <table name>
WHERE SaleDate BETWEEN DateAdd("d", -[Interval], Date()) And Date()
AND [Over/-Short] = True
GROUP BY Assoc#
HAVING Count(*) > 3

When the query runs enter 90 in the query prompt to look at data that
had a SaleDate in the last 90 days. You could also use a Form
reference by replacing [Interval] to something like this:

Forms!FormName!IntervalControlName

IntervalControlName must have the requested day's interval.

You can also have dynamic dates - what you called "rolling dates" as
part of the PARAMETERS declaration. E.g.:

PARAMETERS [Interval] Byte, [End Date] DateTime;
....
WHERE SaleDate BETWEEN DateAdd("d", -[Interval], [End Date])
And [End Date] AND [Over/-Short] = True

Now you can have an Interval as large/small as you want for any date
that you enter [End Date].

See the Access Help articles on PARAMETERS statement and the DateAdd
Function for more info.

====

I still think it's best to use the DatePart and the DateAdd functions
to determine which week, month, quarter, etc. the SaleDate is in,
because then you don't have to maintain the fiscal calendar table.
Imagine what would happen if you forgot to add dates to the table at
the turn of the year. Imagine how big this table will become as your
data grows.

Try playing w/ the formula I gave you for finding your company's
fiscal calendar - remember to use the correct offset. Use the debug
window and just type in date and interval variations to see the
results:

? DatePart("Q", DateAdd("m", -1, #2/1/03#))
1

This formula works because your fiscal year starts 1 month (-1) after
Access' 1st of the year. Therefore, you will always be using the
DateAdd("m", -1, <some date>) format in the DatePart() function. Your
varying values will be the <some date> and the Interval parameter in
the DatePart() function [Interval parameter = "q", "y", "m", etc.].
IOW, if you had a SaleDate of June 27, 2003 & wanted to know its
fiscal day-of-year in your fiscal calendar you'd have a formula like
this:

? DatePart("y", DateAdd("m", -1, #6/27/2003#))
147

Which indicates that it is the 147th day of your fiscal year.

Other examples:

? DatePart("Q", DateAdd("m", -1, #1/1/03#))
4

This proves that Jan will compute as the last quarter in your fiscal
year.

To find week of year in your fiscal year just change the "Q" to "ww":

? DatePart("ww", DateAdd("m", -1, #1/1/03#))
49


HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQC2nDYechKqOuFEgEQJxOgCgjYYq85MaCmz6BhL5D2CVrTc1tLoAoM5l
+4U2egGM4n7SmDye8ZWuasRR
=So+c
-----END PGP SIGNATURE-----
 
Thanks, MG. I'm working on this - it will take me a day
or two, I think.
I have a question, though. I am not familiar with the
Debug window. I could sort of find it, find the toolbar,
but I couldn't activate it. I think I need to take a
class - is it VBA? Do you have any thoughts on the
CompUSA online seminar/stuff? Any advice on a class would
help. My company is owned by my family and I am the IT
person - trying to introduce PCs, reporting, etc., but
there is no one else but me for applications. I hired
someone to help us build our LAN, install and support PCs,
but we're really behind and slow. (I've been with the
company full time only 2 years now. Last year I got them
to stop Typing Invoices on a Typewriter, and pulled
together a little Word Mail Merge thing to create and
print invoices - very 80's, but that's WAY ahead of where
they were). This is why I am so "needy", but willing to
take classes and learn what I need to know.

All suggestions and advice are really welcome.
Sara

-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

For the Over/-Short count > 3 times in 90 days you'd use the WHERE
clause like so:

PARAMETERS [Interval] Byte;
SELECT Assoc#
FROM <table name>
WHERE SaleDate BETWEEN DateAdd("d", -[Interval], Date()) And Date()
AND [Over/-Short] = True
GROUP BY Assoc#
HAVING Count(*) > 3

When the query runs enter 90 in the query prompt to look at data that
had a SaleDate in the last 90 days. You could also use a Form
reference by replacing [Interval] to something like this:

Forms!FormName!IntervalControlName

IntervalControlName must have the requested day's interval.

You can also have dynamic dates - what you called "rolling dates" as
part of the PARAMETERS declaration. E.g.:

PARAMETERS [Interval] Byte, [End Date] DateTime;
....
WHERE SaleDate BETWEEN DateAdd("d", -[Interval], [End Date])
And [End Date] AND [Over/-Short] = True

Now you can have an Interval as large/small as you want for any date
that you enter [End Date].

See the Access Help articles on PARAMETERS statement and the DateAdd
Function for more info.

====

I still think it's best to use the DatePart and the DateAdd functions
to determine which week, month, quarter, etc. the SaleDate is in,
because then you don't have to maintain the fiscal calendar table.
Imagine what would happen if you forgot to add dates to the table at
the turn of the year. Imagine how big this table will become as your
data grows.

Try playing w/ the formula I gave you for finding your company's
fiscal calendar - remember to use the correct offset. Use the debug
window and just type in date and interval variations to see the
results:

? DatePart("Q", DateAdd("m", -1, #2/1/03#))
1

This formula works because your fiscal year starts 1 month (-1) after
Access' 1st of the year. Therefore, you will always be using the
DateAdd("m", -1, <some date>) format in the DatePart() function. Your
varying values will be the <some date> and the Interval parameter in
the DatePart() function [Interval parameter = "q", "y", "m", etc.].
IOW, if you had a SaleDate of June 27, 2003 & wanted to know its
fiscal day-of-year in your fiscal calendar you'd have a formula like
this:

? DatePart("y", DateAdd("m", -1, #6/27/2003#))
147

Which indicates that it is the 147th day of your fiscal year.

Other examples:

? DatePart("Q", DateAdd("m", -1, #1/1/03#))
4

This proves that Jan will compute as the last quarter in your fiscal
year.

To find week of year in your fiscal year just change the "Q" to "ww":

? DatePart("ww", DateAdd("m", -1, #1/1/03#))
49


HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQC2nDYechKqOuFEgEQJxOgCgjYYq85MaCmz6BhL5D2CVrTc1tL oAoM5l
+4U2egGM4n7SmDye8ZWuasRR
=So+c
-----END PGP SIGNATURE-----

Thanks. I am still not there, I'm afraid to say. It looks
like I'm going to be best off to put all the individual
dates in the MapDates table. MapDates has only
WeekStarting and WeekEnding dates, and the data I have is
on each day of the week individually. Also, for the
Associate counting 3, I can try the concept, but since
it's a rolling 90 days, I think having the date in the
MapDate table and doing a date function that will subtract
90 days from "today" (or a date the user enters) would be
best.

Am I right?

I'm sorry to be so difficult with this; it's really a
tough one for me and I'm reading all the help associated
with your suggestions, but it still isn't there. That's
why I'm thinking a table with every day in it might be the
best?
Click to expand...

.
Click to expand...
 
MG -
I started designing and working with DatePart and have
another (I think) problem, where I'll have to go to the
full year by day to make all this work.

The problem is that our months aren't calendar months.
For example, Month 1 is Feb 1 - 28, nice and easy, and
terribly unusual. Month 2 is Feb 29 - April 3 (5 weeks).
Month 3 is April 4 - May 1 (4 wks).

That is why a contractor we had created the "MapDate"
table, but she only needed the weeks, not the individual
dates, as I need. I can't figure how to use DatePart with
this additional complication, though Lord knows I've tried!

Since we have to keep the MapDate table going, it won't be
such a big deal to keep "Full" MapDate going, but as you
say, it would be better if we didn't have to.

Further thoughts?
Sara
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To get to the debug (aka Immediate) window -

Acc97: Open a standard module (under the Modules tab) or form/report
class module (open the form in design view & from the main menu click
View > Code) & hit the Ctrl-G key combination.

AccXP: From the database window (shows tables, queries, forms, etc.),
hit the Ctrl-G key combination.

I can't recommend any classes, being self-taught. You might try a
Microsoft Press book on VBA Step-by-Step (if it exists). Go to a good
book store and look at "primary" books on VB. There was a Developer's
Handbook for VBA, published by Sybex (I believe) that was available
about a year ago - haven't seen it in a while.

You might try any business or community colleges in your area that
give night-classes in Microsoft Office or Access or VBA. Probably
only VB classes will be taught as one-subject class. VBA is usually
part of MS Office classes.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDFpZIechKqOuFEgEQLrkACg7QFkgAjz7k05GaQevvfGpAV0HMsAoKZ2
14Kpqi7DDpDQZpRTTidBPvNT
=UQMu
-----END PGP SIGNATURE-----
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Strange months. In your case use the MapDates table 'cuz using the
DatePart() function as I suggested would be more trouble than it's
worth.

If the MapDates table is set up like this you may be able to avoid
putting in all the dates of the year:

MapDateStart MapDateEnd MapMonth MapQtr
2/1 2/28 1 1
2/29 4/3 2 1
4/4 5/1 3 1
5/2 ? 4 2
..... etc. ....

The columns MapDateStart & MapDateEnd must be TEXT data types for the
following to work.

To find the month or qtr a SaleDate falls in use a query like this:

SELECT I.SaleDate, M.MapMonth, M.MapQtr
FROM ImportFile As I, MapDates As M
WHERE Format(I.SaleDate, "m/d") BETWEEN M.MapDateStart And
M.MapDateEnd

You could use this query in other queries to get the fiscal month &
fiscal qtr.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDFuOoechKqOuFEgEQK1UgCdEfdatAYHYbAnCdNE4FTsjeuSWhoAoOUU
+ggcAY2KODrqQ9AIuXZI0STM
=nULK
-----END PGP SIGNATURE-----
 
Many thanks on all the help. This weekend I'll go book
shopping and see if I can find something to move me ahead
a bit. I continue to work on my problem, but have all the
dates in a table and it only took a few minutes. Any
problems - I'll post a new question!
Again, thanks.
Sara
 
Back
Top