Setting up database

[nebblkshts]

I am trying to set up a database so I can deliver cleaner reports to all involved. I have been tracking printers, copiers, and Large format printers we lease for 4 years now. Every month I take the numbers and list them out into a spreadsheet with total color for that month and total black and white for that month. at the bottom of the spreadsheet, I do the calculations for how much we spent on color and black and white plus the lease cost so accounting will know when they get the bill it is right. Then I generate a separate report for all printers and copiers that are in production so they can see if the numbers are going up, going down, or staying the same. Below that, I show the numbers from the previous year for comparison. Every year I add a new tab and start the year over. I feel like I can do this easier in a database.

1. How do build the table for each copiers, printers, and large format?
2. How do I show the costs?
3. How do I change over every year new database new table?
4. Once I have a database built I would like to work on reporting after the database is set up.

[ranman256]

1 table, tCopiers (or tEquipment to use for other items)
EqID (autonum)
Make
Model
Serial
EqType (copier, scanner, etc)
etc...

tEqUsage table
UseID (autonum)
EqID (long) from tCopier table
ReadDate
Counter
ColorBW (is this reading for color or B/W)

Note: you would not 'change over' to a new table. You keep all data for historical archive.
you COULD have a History database, and dump the year's data to it, then empty the tReadings table and start with 0 records.
But IMHO, I would keep the internal tHistory table and dump it every 5 years or so.

Once the db is build and you have data, build reports. I have a tReports table that shows in a listbox for user to select:
[RptName], [rptDoc]
Monthly Rpt, rMonthly
Weekly Rpt, rWeekly

user sees the RptName, but code opens the rptDoc
docmd.openreport lstRpts,acViewPreview

provide rpts to users via email.
docmd.OutputTo acOutputReport ,"rMyReport",acFormatPDF,vFile
or
docmd.SendObject acSendReport ,"rMyReport",acFormatPDF,sTo,,,sSubj,sBody


If you need multi users to be in the database, (why?) then you must split the db with the tables on a server and give each user a copy of the Front end app.

[nebblkshts]

ok, So I have created the tables but in tCopiers do I add the numbers data? Is the second table for reporting purposes?

Jan Color	Jan B/W	Feb Color	Feb B/W
71	9,893	30	9,524
342	9,399	102	8,944
2,062	93	2,071	100
10,268	180	9,562	212
13,502	393	14,037	357
10,173	4,201	9,682	3,368
1876	3	1,705	2
1424	0	819	0
1,765	68	1,184	28

[davegri]

nebb-Lease-davegri-v01.zip
See if this will get you started. I used ranman's table design mainly, with a small variation from normalization by providing both a color count field and a black and white count field for the subform.

[nebblkshts]

Ok, I figured it out it took me some time to wrap my head around how the numbers would be entered into the tEqUsage table via the relationship. Thanks davegri for help wrap my head around that.

[ssanfu]

I had some free time, so I modified davegri's example - I added a couple of fields for page costs, renamed a couple of fields, created a couple more tables, created some queries and added some forms.