Web Development

Ask something
Discussion Locked

Access: Report - Sorting

0 helpful
4 replies

by soft byte

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

This pertains to an inventory database I am presently working on.

I have items moving to various locations very often. I update their movement in the database, which promptly reflets the same.

Unfortunately, I have one draw back, when I view the report for a particular item, I tend to get a resultant history of the item's movements since its purchase/ acquisition.

How can I create a report, so that I can only get the most current record which shows its present location rather than getting a complete history of its movement.

4 replies

by dr dij

you should have one table setup to include ONLY current location of items. your transactions screen for end users would update this and move between locations, possibly split to allow rainbow pallets (mult sku on pallet).

movement history is irrelevant to current location inventory and should be in a separate database table. you can have sept report to show item history / movement.

by softbyte

Thanks Guys,

The Database I am working on has a separate table called history.

History has fields like Serial No, Date, Location, Department, User and last update date.

"Serial No" in History has 'a many to one' relationship with 'Serial No' in Table 'Equipment'

When I print out Reports, I use a Query to Compile information from the Equipment Table, the Purchase Table, the Location Table & a few other Tables.

The Report I print out gives me the history of the location of the Item from the date of its Purchase.

I would want my Report to only give me the latest (or current) location of each item in the Report.

How can I do that? - How can I get the latest report out of the History Table to give a report of the current location of all items, rather than a complete history of all items.

by skennedy10

Soft Byte,
A little more information would be helpful. You will want to make a totals query on the table (probably) that has the date field. In that Query have only the movementdate field and the Primary Key, which is your SerialNo. Click the SIGMA button (Totals button) on the toolbar. The SerialNo should have "Group By" on the totals line and Max (or last if that does not do it) on the date field. This will give the last date for each item. Then create another query using this one and the other data. If you are just doing a report for one item, put a parameter on the SerialNo field to pull just that one item. Since this is the "many" side, you should not have to worry about the join type with the other data, but if not all items show up (caused by not having data on one side) you may have to do an outer join. Get back with me if you need more instruction. This is just off the top of my head and I cannot see your second post.
Skennedy10@sbcglobal.net

This discussion is closed to new replies.