Re: HELP !!! - Complex scan with DATES and SunDialServices DateTran table

Daily$ $100
BGN 3/15/1999
END 4/1/2002
JAN $6600
FEB $6600
MAR $7806.45
APR $6673.33

this is what I would expect to see for the above date range

So... would this attack work:
- Ensure your "Calendar" table has a record for EVERY day, or at least
every WORKING day
- It should contain, at least, the actual Date, the Day-No-of-the-week
(0-6 or 1-7, and know which codes are Sat/Sun), the Month-No (1-12), and a
flag - 1="Worked", 0="Non-worked".
- Populate all fields, set that Worked-flag=1 on Mon-Fri, insert the
Month-No, etc...

Run a Query:
- On the Date field, say ">=~BGN, <=~END"
- Put a CHECK-mark on the Month-No field
- Run a "Count" or "Sum" on the Flag field

You'll get an answer of the No. of worked days, per month, spanning all
relevant years.

Run another query to multiply the Sum of worked-days by the Rate.

No "code"... though you might need some to extend the Calendar table - as
the need arises...

You could replace the above flag with the Daily-Rate itself (22.00), and
then just "sum" these in a single query - per month. This would allow
different rates over time...

- Mike



.

Relevant Pages

  • Re: Report is making too many numbers
    ... from the $'s being listed more than once in the query. ... And you want to display the sum of in the Vendor header or footer ... My report is based on a query. ... those records--you could create a Totals query by depressing the ...
    (microsoft.public.access.reports)
  • Re: identify semi-duplicate records, and amend elements of those r
    ... Your idea was my first thought...so I relaxed that rule by allowing the Sum ... called OR how its used specifically within the query may have an impact? ... It could be that the Sum(HH CCY CFAMT) is not returning exactly ZERO. ... the select statement, and filled in specific criterion by criterion, to see ...
    (microsoft.public.access.queries)
  • Re: Date Range Totals for Logical Fields
    ... Query reformatted for ease of reading ... Lead Date Lead Date By Month Signed Up Sum Of Active Recruit Sum Of Info ... Recruits Kits Sent Recruits Contact Invites ... Then put your criteria there. ...
    (microsoft.public.access.queries)
  • Re: running sum in a query for a calculated field
    ... Make a first query like: ... make the running sum, in the query that does the said running sum. ... FROM qSUM AS a INNER JOIN qSum AS b ... PlannedlCumulativeCount, qryMaintbl.dir ...
    (microsoft.public.access.queries)
  • Re: 2 combo boxes
    ... Providing Customers A Resource For Help With Access, ... Select the Calendar Control. ... which would run the query for all entry dates between the monday ...
    (microsoft.public.access.forms)