[Scleros]

I'm working on moving my records from Excel to Access. The database so far is a table of assets, a table of Categories for grouping in a report (only 2 category records, Office Equipment and Computer Equipment), and a DateRange table to group report records by specific year periods with friendly group header label. The report is based on query:

SELECT tblCategory.Label, tblDateRanges.[Label], tblAsset.ID, tblAsset.AcquisitionDate, tblAsset.AcquisitionCost, tblAsset.Vendor, tblAsset.Description, tblAsset.ServiceStartDate
FROM tblDateRanges, tblCategory INNER JOIN tblAsset ON tblCategory.ID=tblAsset.[Category ID]
WHERE (((tblAsset.ServiceStartDate)>=[StartDate] And (tblAsset.ServiceStartDate)<=[EndDate]))
ORDER BY tblCategory.Label, tblDateRanges.[Label] DESC , tblAsset.ServiceStartDate DESC;

Table structure below:

tblAsset
ID
CategoryID
AcquisitionDate
AcquisitionCost
Vendor
Description
ServiceStartDate
ServiceEndDate

tblCategory
ID
Label

tblDateRanges
ID
StartDate
EndDate
Label

I'm stuck on how to add the percentage discount into either the year, category, or another table so that the assessed value calculations are applied like so:

Office Equipment category:
Year 2011 - 90%
Year 2010 - 80%
Year 2009 - 70%
Year 2008 - 60%
Year 2007 - 50%
Year 2006 - 40%
Year 2005 & Prior - 30%

Computer Equipment category:
Year 2011 - 70%
Year 2010 - 50%
Year 2009 - 30%
Year 2008 - 15%
Year 2007 - 10%
Year 2006 - 5%
Year 2005 & Prior - 2%

I setup a percentages table like below but then didn't know how to implement it in the report.

tblPercent
ID
CategoryID
DateRangeID
Discount

Ideally the report would prompt for current year when run and generate the DateRanges and Percentages information on the fly.

Sample report attached.

Also, who/what provides good Access training?

[ScottGem]

OK, so what you need is to apply the depreciation percentage to the total per year, per category. You could add this into your query. You could link it to each record on Year and Category. Then just use the amount in the footer for the group. I would try that first.

An alternative would be a DLookup to retrieve the percent.