I am trying to design a report in a Microsoft Access database that will allow me to keep a current record of what equipment is available. I also need to show where the equipment is located if it is not in the warehouse. I really don't know where to begin so any help would be greatly appreciated. Thanks in advance.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Help!
- Thread starter Khris821
- Start date
- Thread starter
- #3
I have only designed a few databases. Most of them were small and rather uncomplicated. As far as I can tell, this database is normalized already. The tables that I have set up that are needed for this report are:
Equipment: EquipmentID, EquipName, EquipDescription, etc.
Orders: OrderID, EmployeeID, JobID
OrderID: OrderID, Date, EquipmentID, Quantity, etc.
Jobs: JobID, misc. information
I was not sure if I should create another table connected to Equipment showing whether or not the equipment was currently available or if I should add this field to the existing table.
Equipment: EquipmentID, EquipName, EquipDescription, etc.
Orders: OrderID, EmployeeID, JobID
OrderID: OrderID, Date, EquipmentID, Quantity, etc.
Jobs: JobID, misc. information
I was not sure if I should create another table connected to Equipment showing whether or not the equipment was currently available or if I should add this field to the existing table.
You could create a Location table with a LocationID field, and also include a description field to hold information on specific locations in the warehouse (if that makes sense), and also descriptions for locations outside the warehouse.
Then add a LocationID field as a foreign key in your eqipment table.
It sounds like you might need a status field in your equipment table as well, to indicate whether a piece of equipment is available or not. If there are multiple possible statuses then you may want to create a separate table for that as well and add a StatusID field to your equipment table.
A lot of this depends on what your data needs are, I am just guessing based on some of the things you mentioned.
Then add a LocationID field as a foreign key in your eqipment table.
It sounds like you might need a status field in your equipment table as well, to indicate whether a piece of equipment is available or not. If there are multiple possible statuses then you may want to create a separate table for that as well and add a StatusID field to your equipment table.
A lot of this depends on what your data needs are, I am just guessing based on some of the things you mentioned.
I'm a little unclear as to the orders and ordersID tables. Is the OrdersID table supposed to be like an orderItem table? If so, you should probably have an
OrderItem: orderItemID, orderID, ...
Order: orderID, ...
I think lynchg is on the right track with a location table. You may also consider adding purchase price information to the equipment table. And, I'm assuming you have an Employee table to satisfy the EmployeeID field.
How what other information do you need in designing this db?
Andrew
a.k.a. Dark Helmet
"What's the matter Colonel Sandurz? Chicken?
OrderItem: orderItemID, orderID, ...
Order: orderID, ...
I think lynchg is on the right track with a location table. You may also consider adding purchase price information to the equipment table. And, I'm assuming you have an Employee table to satisfy the EmployeeID field.
How what other information do you need in designing this db?
Andrew
a.k.a. Dark Helmet
"What's the matter Colonel Sandurz? Chicken?
- Thread starter
- #6
Drkhelmt...
The database is supposed to be able to allow an end user to enter an order for many specific items that will be delivered to one location. Will the OrderItem: OrderItemID, OrderID... and Order: OrderID tables you suggested allow me to do this.
Lynchg....
I am in the process of trying your design suggestions out right now. Thanks.
Please forgive me for any silly questions. It has been quite some time since I have worked with Access. Thank you both for your help. It's appreciated.
The database is supposed to be able to allow an end user to enter an order for many specific items that will be delivered to one location. Will the OrderItem: OrderItemID, OrderID... and Order: OrderID tables you suggested allow me to do this.
Lynchg....
I am in the process of trying your design suggestions out right now. Thanks.
Please forgive me for any silly questions. It has been quite some time since I have worked with Access. Thank you both for your help. It's appreciated.
Yes, because for every order (orders table) you have multiple order itens (orderItems table). The location is a function of the order (assuming all items get shipped to the same location).
When you design your forms, you have a Order form that is tied to your order table. Then you have a subform that is tied to your orderItems table. This way, the user only has to enter the order information once and all the order items get tied to that.
Hope this gets you closer to the solution.
Andrew
a.k.a. Dark Helmet
"What's the matter Colonel Sandurz? Chicken?
When you design your forms, you have a Order form that is tied to your order table. Then you have a subform that is tied to your orderItems table. This way, the user only has to enter the order information once and all the order items get tied to that.
Hope this gets you closer to the solution.
Andrew
a.k.a. Dark Helmet
"What's the matter Colonel Sandurz? Chicken?
- Status
Similar threads
- Locked
- Question
- Locked
- Question