Here are the tables.
Do you want to see data from the most recent location record on a layout based on the Item table?
Is there a date field in the Location table that records the date on which that item was either moved to that location or re-assessed to a new value?
Hi, yes, I have image shots above - not sure if you can see them. In my first post, the accession sheet is to the right of the data entry layout. It would list everything in the Item Table pertaining to that particular item. At the bottom of the accession sheet, it would have all the information from the most recent inventory taken.
To the left is a screen shot of the data entry screen. I entered a bunch of different dates and weird amounts but it just isn't being pulled to the accession sheet.
I can see the uploaded images, but that doesn't answer the crucial question: Is there a date field in the Location table that records the date on which that item was either moved to that location or re-assessed to a new value?
Thanks. In the Location Table, I have a _FK field that is a number (Inventory ID#) but is actually the date - i.e. 121212 is 12/12/2012. The date that the inventory was taken is in another table, InventoryDate. I created this new table because I needed to list who cataloged the items and their contact information.
I took the accession sheet and used the Location Table but it isn't pulling from the most recent record. Any help on how to pull the information from the most recent records automatically? Say if I wanted to batch print a whole group?
Sorry, but not only is that FK field a less than optimum design choice, it won't serve this purpose. You need a field in the location table that, for a given inventory item, has a different date in each record--the date that records when that item was moved to a different location or reassessed and a new value recorded.
Unless I am missing some detail here, not only will that FK field not be that date, but two inventory items might easily have been added to inventory on the same date and then they would have the same FK value and this would result in two records in inventory that would match to the same record or records in Location. That would be a recipe for disaster.
So I am looking for something like this:
ItemID Description Value Location Date
1 Urn $300 Collection X 5/31/2014
1 Urn $250 Collection X 6/1/2013
1 Urn $250 Collection A 3/3/2011
With data like this, I can tell that the first record (the first row) is the most recent record for ItemID #1 and I can set up relationships that will make it easy to refer to this specific record for Item ID = 1. I could also make this work if the records in Location have a field with an auto-entered serial number as I can just as easily refer to the related record ( a record where ItemID =1), with the largest serial number as I could the record with the most recent date.
But without some such field, we have a serious problem with your data. In a perfect world, the most recently recorded location record will be the last record in an unsorted relationship or an unsorted found set of records, but this is not a perfect world. You reported in your other thread that you accidentally deleted some of these records and had to import them from a back up file. Such imports, which make the imported records the "last" records, can alter the results you get when you look for the "last" record.
If you truly don't have a field that will serve for this in location and you are confident that imports haven't messed up your unsorted order, I suggest that you add an auto-entered serial number field to this table, pull up an unsorted found set of all location records on a location layout, and use Replace Field contents with the serial number option (and click the check box to update auto-enter options) to assign a serial number to all existing records. You can then sort a relationship to Location by this serial number in descending order to make the most recent location record the first related record and you can then access any field in this most recent record very easily.
Hi, again, thank you. All my tables have a field with auto entered record numbers. I'll double check that when I re-imported the files that the record numbers are correct, otherwise I can renumber them. I do have everything that you asked but probably didn't explain it very well.
"You need a field in the location table that, for a given inventory item, has a different date in each record--the date that records when that item was moved to a different location or reassessed and a new value recorded." Yes, I have that.
"I could also make this work if the records in Location have a field with an auto-entered serial number as I can just as easily refer to the related record ( a record where ItemID =1), with the largest serial number as I could the record with the most recent date." Yes, I have that.
I can print using a Go To Related Record button but was hoping I could batch print if necessary.
Again, thank you so very much.