Wondering if somebody could help me with idea's

[deiniolj]

Need to change this old Stock sheet our department has to something more accessy. Thing is I just can work itself round it..

Basically we have stock coming, then we allocated it to students. When the stock has gone to the student we highlight it in yellow.

I know very old school, need something better.



Any idea's to push me in the right direction would be appreciated.

Deiniol

[rpeare]

Can you explain how you're currently using this spreadsheet is this how it goes?:

It looks like you get an order on a specific date and assign it a unique identifier (the Order No column). I'm assuming the people's names you assign your orders are the people that actually receive the computer but do you know those names as soon as the order is filled out or are they filled in as computers become available? Is your marking the data in yellow purely so it's easy to see if the order is completely filled so you can ignore it when you go back and look at the spreadsheet again? Does the historical data have any significance? Are the computers given to the students 'permanent' (are those computers forever removed from your inventory) or can your computers re-enter your stock after a semester (computers are on loan or rented for a term) How you design your database depends greatly on the rules governing how and for how long you assign your computers to people

[deiniolj]

You are correct on all things except for 2. The students get to keep the laptops, and we know who get a laptop before hand. There names are put into the spreadsheet first, then stock is ordered.

Thx
Deiniol

[rpeare]

Ok so the structure you want is something like this:

Tbl_People (list of students and whatever information is relevant to them with a PK (autonumber) for each student, this would include things like first, last name, school assigned student ID, etc)
Tbl_Order (list of basic orders with a PK (autonumber) this would include the order number, order date and anything else that's related directly to the order as a whole (like maybe department or something))
Tbl_ComputerType (information related directly to the type of computer being issued PK (autonumber) make/model etc)
Tbl_Inventory (list of all items received this is basically your computer inventory PK (autonumber) FK to the tbl_ComputerType table and any computer specific information (like serial number))

Tbl_Order_Detail (this would contain the FK to Tbl_Order, FK to Tbl_Inventory, FK to Tbl_People) and any information relating to specific detail related items (maybe notes like 'will pick up on x day')

Since you're not worried about returns you don't really need an inventory management setup but you could put in things like unassigned computers etc. This is a very basic setup but should do what you want it to.

[deiniolj]

How does this look for Starters

I will need to add some way of automatically changing the QTY in stock, but I will figure that out when I have figured this table structure out..

[rpeare]

I can't read 2010 databases, please convert it to 2003 (preferably) or 2007.

[deiniolj]

Hopefully this will do..

[rpeare]

how are you going to identify individual computers in your database, your inventory table doesn't have a way to do that.

Let's say you have two students with the same model of computer, they both go to a class during the class one of the computers is stolen, these two students are sitting by one another Person A picks up the remaining computer thinking it's theirs when it actually belongs to person B, the only way to identify them would be through some sort of serial number catalog or else an internally generated identification attached to each item.

Also for your students I would store first and last name separately and have their internal school ID as part of that dataset as well so you could search by their name or student ID (if their name is misspelled in the table or their student ID is recorded incorrectly)