I have a project that I'm going to be starting soon, hopefully, and i wanted to see if anyone had any ideas on how what i should do. I work for a company, and we have 5 different offices around the US. We have test equipment that travels around to the different office locations, and we are trying to see about creating an Asset Tracking System in Access. Each item has an ID, Mfg, Model, Serial, and Description. I want to know whats the best way to type in the ID number, pull all the information from that item, and log it into the database (like a Check OUT.) On the other hand, when it leaves our location in CA, when it arrives in FL, how can they open the same database, and log in that the item is at there location. Hope I'm making sense, i haven't worked in Access for a long time, and 2007 is making me lost.
So here's the idea:
*Test Equipment AB1234 needs to get shipped to Florida.
*We open a form, Scan a barcode that has AB1234, it pulls all the information. That information is saved somewhere in the database.
*It arrives in Florida, and they can input somewhere that that item is now at the Florida Location.
You can create an inventory table holding the information about the eaquipent, then a second table collection information about the moves (date of shipment, destination, date of reception etc. This second table should have a forign key linking it to the inventory table, both tables being related by a one (inventory) to many (movements) relationship.
This is only a slight advice, a more in-depth analysis would probably yield a more complex schema, but it can be a beginning.
What you have to do really is a central database. The Assets are the same but the locations change, whilst it is a good idea to manage Movements lets start with a Location on the Asset and Location Table.
Secondly does someone who needs Test Equipment AB1234 may need to find it and when that piece of test equipment will be available?
Thank you both for replying back. People really don't have to know when the equipment is available, only where and who has it. So if someone in California needs a certain test equipment, they can see in the database that it was received in Florida, by this person, on this date. We really don't want a real complicated database. We want to be able to fill out a form and log a check-OUT and check-IN. The main idea is in that in a form, we can type an ID number (AB1234) and pull up all that test equipment details (Manufacturer, Model, Serial, & Description). The only information that needs to be inserted is date, destination, and name. I did what Sinndho said. I create a inventory table that has: Equipment ID, Manufacturer, Model, Serial, & Description. Whats the best way to pull up the Equipment details into the form through the ID #? Is this done in in VBA? DLookup? Then is this when the information in insert into the Movements table/Check-OUT?
You need to create a web based solution so that all the sites can access the data. Either by creating a web application or something like a Terminal Server or wait for Access 2010 which is reputedly browser friendly.