By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,710 Members | 1,613 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,710 IT Pros & Developers. It's quick & easy.

Need to separate different dates wihtin a report

P: n/a
I have a database that records a part no, a scrap reason, the cost of
scrapping that item, and the date. I need to porduce a report that
will show up to a weeks worth of data at a time, but I need each dates
scrap amout totaled and each day to be separated on the report. I am
self taught at access and still a beginner, so please be gentle on
your replys.

Thanks for your time in advance...
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"Brian Coy" <br******@accessky.net> wrote in message
news:ac**************************@posting.google.c om...
I have a database that records a part no, a scrap reason, the cost of
scrapping that item, and the date. I need to porduce a report that
will show up to a weeks worth of data at a time, but I need each dates
scrap amout totaled and each day to be separated on the report. I am
self taught at access and still a beginner, so please be gentle on
your replys.

Thanks for your time in advance...


In design view of the report bring up the Sorting and Grouping dialog and
add an entry for your date field. Add Group Headers and Footers as
necessary and in either of them you can put a TextBox using =Sum([scrap
amount]) to give you the total for that date. You can also use the
"ForceNewPage" property for the Group Footer to indicate that you want each
date's data to start on a new page.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #2

P: n/a
DFS
"Brian Coy" <br******@accessky.net> wrote in message
news:ac**************************@posting.google.c om...
I have a database that records a part no, a scrap reason, the cost of
scrapping that item, and the date. I need to porduce a report that
will show up to a weeks worth of data at a time, but I need each dates
scrap amout totaled and each day to be separated on the report. I am
self taught at access and still a beginner, so please be gentle on
your replys.

Thanks for your time in advance...


Brian,

You're recording the correct pieces of data, so all you need is a report to
show the daily total. Hate to say "check the Help" but if you play around
with the Report Wizard, it'll do exactly what you want automatically. All
you need is a scrap date group and a scrap date total.

If you're just curious about the daily scrap totals, and don't want to see
the details of each item scrapped every day, run this query:

SELECT ScrapDate, Sum(ScrapCost) as DailyScrapCost
FROM Table
GROUP BY ScrapDate;
If you want to see, high to low, the most "expensive" scrap reason, try
this:

SELECT ScrapReason, Sum(ScrapCost) as DailyScrapCost
FROM Table
GROUP BY ScrapReason
ORDER BY Sum(ScrapCost) DESC;
If you want to see scrap totals by month, try this:

SELECT Format(ScrapDate,"m") AS Month, Format(ScrapDate,"mmm") as MonthName,
Sum(ScrapCost) as MonthlyScrapCost
FROM Table
GROUP BY Format(ScrapDate,"m"), Format(ScrapDate,"mmm")
ORDER BY Format(ScrapDate,"m")



Nov 12 '05 #3

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need 2 Groups in the report: Days and Weeks. In the Sorting and
Grouping dialog box set the Field/Expression to the scrape date and
the Interval to Week. Enter the scrape date under the first date and
change the Interval to Day. For each grouping set the Group Footer
property to Yes.

In the report's Detail section you can put the individual items that
are scrapped each day. In the Day Footer section You'd put the a
Control with a ControlSource like this:

Control Source: =Count(*)

Do the same for the Week footer. This will keep a count of the number
of items per day and per week.

The report sections should look like this:

Report Header
Page Header
Detail
Day Footer
Week Footer
Page Footer
Report Footer

HTH,

MGFoster:::mgf
Oakland, CA (USA)

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

iQA/AwUBP8uQYIechKqOuFEgEQK3AgCdEE6JyY3YVQa/CAJSyIgv8JKgn7EAnjZm
qP2/szNVwPVusgAH1FYxr1wC
=AGPh
-----END PGP SIGNATURE-----

Brian Coy wrote:
I have a database that records a part no, a scrap reason, the cost of
scrapping that item, and the date. I need to porduce a report that
will show up to a weeks worth of data at a time, but I need each dates
scrap amout totaled and each day to be separated on the report. I am
self taught at access and still a beginner, so please be gentle on
your replys.

Thanks for your time in advance...


Nov 12 '05 #4

P: n/a
Thanks to everyone for their help, somehow I missed that option in the wizard...
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.