Complex? database question

[chris.leng]

I currently have 2 excel spreadsheets, one is relatively stable data referring to manufacturers of parts, order numbers etc, the other is more dynamic as it records data of individual shipments to and from me

The problem is I currently have to enter most data twice as it needs to go onto both spreadsheets, i know the obvious thing to do is make an access database, however i only have a basic understanding of how to use one (i know a bit about using forms and reports and the master table behind them) and one of the sheets has a rather dynamic nature.



So question one, once i’ve done a report of the items to send, how do i then deduct the quantities from the total stock?



The second is more complicated as it relates to the second sheet.
When an item is delivered i record its location, its quantity and the shipment number it came in under as items need to be traceable back to manufacturer. When an item gets sent out this data has to be returned so i can find the item and add its original shipment number to the paperwork which accompanies it.
Once a delivery is exhausted these values would need to change to the new values of another delivery I received as it will likely have been stored in a different location and will definitely have a different shipment number, is it possible to replicate this procedure automatically in Access?
possibly by tying the zeroing of a quantity to an update function or something?


Also would it be possible to keep a record of the “items sent out reports” as part of the database as this is also useful for traceability.

 

[Pat Hartman]

Your inventory needs seem to be half way between serialized and bulk. With serialized, you would keep an individual stock record for each part. With bulk, you would just keep a count of what you have on hand. You seem to have a bulk requirement but with multiple buckets - one for each shipment.

Search for "inventory" and "stock take" in the archive index for posts that will help you get started.

BTW, welcome aboard

 

[chris.leng]

i had a search around, this guy

http://www.allenbrowne.com/AppInventory.html

seems to be talking about roughly what i want... i think? though i'd need to add some extra values to those tables he shows in that picture.

where would i be able to find a database set up like this? or how would i go about linking those 6 tables together?

also all this is assuming the "Acquisition Detail" table would contain different "prices" etc for an item and automatically remove the price of a single delivery once it was used on a first in first out basis.

 

[Pat Hartman]

I doubt that you will find a sample that meets your needs although you may find one you can purchase. Also take a look at the Northwinds database. The newest version may actually have the inventory stuff coded although earlier versions didn't.

 

[chris.leng]

I've been looking at that northwind database and am starting to make sense of it, got some noobie questions though.

The expand box, the little + next to the records of some tables which makes a drop down to show another sub table, how do you add those?
are they a sub table? or a link which searches another table and returns information?

also when making a new table there is always a unique identifier column for each record. why is this not present in some of the tables in the northwind example? has it just been replaced by another unique identifier?


another thing i noticed while trying my own database is some odd behavior of the unique identifiers, when i work on the first record directly onto the table the identifier changes each time i enter another field of that first record, as soon as i move to the 2nd record it becomes fixed so the table appears to start at the 6th record for example, why does this happen?

 

[Pat Hartman]

They are sub datasheets. They appear automatically if you define relationships. If you have multiple many-side relationships, I'm not sure how Access picks which one to default to but you can change that by opening the table in design view and changing the subdatasheet property.

However, isn't there always a however Subdatasheets are more cute than useful. You should be using forms rather than tables or queries so you would be building subforms to show data in the dependent relationships. The general recommendation for efficiency is to set the subdatasheet value of each table to none rather than auto or a specific table.

The only time I have used subdatasheets is to show history in those applications where I store changed versions of a row.

 

[chris.leng]

So the northwind example uses forms to make those sublevels automatically from other tables instead of making subtables for that table?

also any comment on my earlier unique identifier questions?

 

[Pat Hartman]

I haven't looked at Northwind in a while. It may use subdatasheets. Northwind is designed to be simplistic so it doesn't always do things the way a professional would.

If you are changing data in a table for an existing record and the autonumber keeps changing, I would jump right out the window because there are spooks in the house If this is happening on a form, it is most likely your own code that is faulty.

 

[chris.leng]

it only happens on the first record of a new table.

edit: oh, btw, where is the archive index? (i'm probably being slow, but i'm looking for a forum with that name)

 

[Pat Hartman]

Use the Advanced search to see stuff that is older than your profile is set to view automatically.