[ms office - 2010] Access - Simple way of registering a date an item was audited

[CHR1S]

I need a simple way of registering the date an item was scanned with the bar-code reader for audit.

 

It needs to store multiple dates for each item to track its lifetime in school.

 

I then need to create a statement that flags a tickbox for the record on each asset registered.

 

If "date = anydate in 2012"

then "2012flag = true"

 

I will create a simple form that has a bar-code entry field and a button that commits the record and date to a table, allowing me to audit as fast as possible.

 

 

How would you create the table to store multiple incremented dates that you can query for a single record?

 

Thanks

[limbo]

It is simpler to have two tables - one that has all the asset information and one that has each instance of it being scanned.

 

Then you write into the main asset information the latest date an item was scanned (either as it happens or via a query afterwards).

 

And rather than trying to track whether something was done in 2012 or not, it is easier if you then just produce a list of all the assets sorted by the date in ascending order and work your way down the list. Doing it this way will let you pick it up whenever you have downtime and work your way down the list until you can see that the dates are in or around 12 months.

[CHR1S]

This is solely for a quick method of displaying and creating the master audit flag for each year.

 

I have systems in place to audit room changes and faults.

 

Ive gone down the route of creating a seach page with tick boxes and a save button so I can quickly scan and flag multiple items in succession. Its crude but it works very well and its quick.

[jinnantonnixx]

@limbo is right.

 

It's far better to have an associative table for better data normalisation.

 

e.g.

 

Scan Table

-----------

Object ID | Scan date

Object 14 | 06/08/2012

Object 13 | 05/08/2012

Object 14 | 03/09/2011

Object 12 | 03/09/2011

 

As for your query, you could use a "SELECT DISTINCT" from the scan table, with an "ORDER BY scan_date DESC" to get the latest scan date for an object.

Edited August 6, 2012 by jinnantonnixx