Moving records fluidly between tables

[ajs112]

Hi all,



I'm completely new to the whole access world and found this place to be incredibly helpful thus far. I thank everyone in advance for any help they might offer!

Some background: I'm just a dumb firefighter with little programming experience and am designing a database to keep track of a state's surplus fire property to be doled out to volunteer departments as I see fit.

I have a table for surplus property that includes what department currently has the surplus property, what type of equipment it is and additional equipment information. I also have a table for "pending transfer property" to track what pieces of property I have decided to potentially assign to a fire department from the state's surplus inventory.

My problem comes when I need to transfer a piece of equipment from one department to another. I would like the surplus property table to automatically remove the property from the surplus property table to the pending transfer table so that it will not be listed on a pending transfer and in the states surplus at the same time. On the flip side, if a transfer falls through I'd like that record to repopulate in the surplus inventory again.

I'm sure this is clear as mud, and as I'm new to database keeping as well as Access I appreciate any help, ideas, or rearrangements I could make. I'm open to everything, just keep in mind you may have to explain in layman's terms as I'm not a programmer.

Thanks all,

A dumb fireman!

[ranman256]

You would build a macro that has
an append query to take the item from 1 table to the other.
then a delete query to remove if from the source table.

Then another macro to do the same in reverse.

But I think youd have an inventory table where nothing gets deleted, and an owership table that has the Inventory key and the owner.
This way no inventory is actually deleted, just the ownership keys are changed, or empty.
(tables)
tInventory
tHouses (or whatever you call the places where inventory goes) these records never get deleted
tOwners (what house owns what Inv. these records will add / delete all the time)

[ajs112]

Awesome! I appreciate the input. I had a sneaking suspicion that queries would solve my problem, so off to research queries it is!

Yeah, there is a national database that keeps track of the assigned state inventory and where it is housed. For my purposes I'm just keeping track of what departments have excess that is still with them and marking it as available to be assigned to other departments. (This all happens outside the national database and is just for my records) Then, if a transfer occurs or we sell it I log into the national database and update the final records there. This is just so I can keep track of pending orders before I change the records completely and not necessarily keep track of every piece of equipment we have given out.

Thank you much!