Counting records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have used a textbox =1 and running sum over all to label the lines on a
report but can you do it in descending order. I have a report of amount
received at a couple of Christmas kiosks and I'd like to count down to the
last day open, which would be Christmas Eve, and the last line being the
number of days til then. Not a real important item but wondered if there was
a way. Also is there a way to use maxrecords on a report. I have all the
totals from othere years and want to list them side by side but don't want
all the records from previous years showing until I need them. I got around
it on this report by making a temporary table with just the records I needed
by counting back from Christmas. Thanx
 
Gil

I'm not clear from your description, but wonder if you could use a query
instead of a temp table?
 
Thanx for the response Jeff.
I have a table w/ fields Totals2000,Totals2001, etc and after each day of
the current year I enter the total received in the next record of Totals2005.
I always start on the Fri b/4 thanxgvng so there are a different # of days in
each year (unused days having a default of zero). The table looks like this:
(just sample data; there are 35 records in the table)

Totals 2000 Totals2001.......Totals2005
0 0 0
1200 0 0
1300 2100 0
1800 1700 1250
1760 1800 1500
2100 1549 0
1600 1900 0
etc
B/4 I open the report (based on a temp table) I go to the last record and
moveprevious until I get a non-zero entry in the current year- then I
subtract that from the # of records and only copy that many to the temp table
( in the example that would be the first 5 records). Is there a way to do
this with a query (or sql statement). Also I'd like to number the lines,
counting down to Cmas Eve. Could I include a field in the query that might
do that too instead of using a running sum overall. I hope this is clear as
to what I am trying to do. Any other suggestions??
Thanx
 
Gilbert

Nope, I'm still a little fuzzy...(maybe the season?<g>).

A comment: your table design, with fields named "Totals2000", "Totals2001",
.... and (I think, fuzzy remember) one row per day-of-year, might work for a
spreadsheet, but will create headaches for both you and Access.

Unless you normalize your data structure, you will not be able to (easily)
use the features and functions that Access offers.

The following may not reflect your situation, once again because I don't
have a clear picture.

It sounds like you are documenting items (I don't know if they are of mixed
type or all the same thing) received at a location. It sounds like you are
noting either that a specific item was received on a given date, or only
that 1250 items were received on a particular date (and I'm not sure how the
location/kiosk fits in here).

If you need to know/track the number of items received on a specific date,
or during a specific date range, or for a specific year, this is a simple
query if your data is structured something like:

tblItemsReceived
DateReceived
ItemCount

You can use queries in Access to determine days-til-Christmas for any row:

DaysTilChristmas:
DateDiff(DateSerial(Year(Date()),12,31),[DateReceived])

You can use queries to determine total received:

the Sum of ItemCount for DateReceived Between YourFirstDate And
YourSecondDate

These are untested aircode/samples. Your syntax may vary.

Best wishes for the holidays!

Jeff Boyce
<Office/Access MVP>
 
Back
Top