Access database
- or -
Post a project like this3838
$30/hr
- Posted:
- Proposals: 2
- Remote
- #333718
- Awarded
Description
Experience Level: Intermediate
Estimated project duration: 3 hrs
I need to develop a database to do supply allocation by tomorrow Oct 1st.
Attached are the instructions and sample database, and I am available to do a call ASAP!
There are 4 tables to reference,
1. Demand with a list of all parts for a project
2. Schedule with the dates for the project
3. Inventory file
4. Purchase Order file
I am trying to create a query in access to:
1. Create a new table
2. Sort Table 1 by the dates in Table 2. (The identifiers of the records will 3 different columns, aka Project, Line and Stage) (If done manually in excel I would do a concatenate of the 3 columns to ensure its unique, not sure if this can be done in access)
3. Add 3 columns to the new table
a. Supply Type
b. Supply Quantity
c. Purchase Order
d. PO Promise date
4. The demand table will have Part Number and QTY needed
a. If Table1.[QTY needed] Table3.[QTY_AVAILABLE] Table4.[QTY_LEFT] = Planned Order
i. Supply Type = “Planned order”
ii. Supply Quantity = Blank
iii. Purchase Order = Blank
iv. Promise date = Blank
What Im not sure how to do:
The QTY_Available is a rolling number- if you used it in a record previous to the one you are looking at, it must be QTY_Available – (Sum of qty needed of previous records)
Tables to reference:
Table 1: Demand table
Table 2: Schedule
Table 3: Inventory
- Always need to do [QTY_ONHAND – QTY_RESERVED ]= QTY_AVAILABLE when referencing this table
Table 4: Purchase Orders
New table would look like:
Line Stage Date Part Number QTY Supply Type Supply Quantity Purchase Order Promise Date
A123 L01 Assembly P12345 2 On hand 2
A124 L02 Assembly P12345 2 On hand 2
A125 L03 Assembly P12345 2 On hand 1
A126 L04 Assembly P12345 2 Purchase Order 1 30580 1/2/2012
A123 L01 Packaging P23456 2 Purchase Order 2 30580 1/2/2012
A124 L02 Packaging P23456 2 On hand 2
A125 L03 Packaging P23456 2 On hand 2
A126 L04 Packaging P23456 2 Purchase Order 2 30580 1/4/2012
A123 L01 Customer P34567 2 Planned Order
Attached are the instructions and sample database, and I am available to do a call ASAP!
There are 4 tables to reference,
1. Demand with a list of all parts for a project
2. Schedule with the dates for the project
3. Inventory file
4. Purchase Order file
I am trying to create a query in access to:
1. Create a new table
2. Sort Table 1 by the dates in Table 2. (The identifiers of the records will 3 different columns, aka Project, Line and Stage) (If done manually in excel I would do a concatenate of the 3 columns to ensure its unique, not sure if this can be done in access)
3. Add 3 columns to the new table
a. Supply Type
b. Supply Quantity
c. Purchase Order
d. PO Promise date
4. The demand table will have Part Number and QTY needed
a. If Table1.[QTY needed] Table3.[QTY_AVAILABLE] Table4.[QTY_LEFT] = Planned Order
i. Supply Type = “Planned order”
ii. Supply Quantity = Blank
iii. Purchase Order = Blank
iv. Promise date = Blank
What Im not sure how to do:
The QTY_Available is a rolling number- if you used it in a record previous to the one you are looking at, it must be QTY_Available – (Sum of qty needed of previous records)
Tables to reference:
Table 1: Demand table
Table 2: Schedule
Table 3: Inventory
- Always need to do [QTY_ONHAND – QTY_RESERVED ]= QTY_AVAILABLE when referencing this table
Table 4: Purchase Orders
New table would look like:
Line Stage Date Part Number QTY Supply Type Supply Quantity Purchase Order Promise Date
A123 L01 Assembly P12345 2 On hand 2
A124 L02 Assembly P12345 2 On hand 2
A125 L03 Assembly P12345 2 On hand 1
A126 L04 Assembly P12345 2 Purchase Order 1 30580 1/2/2012
A123 L01 Packaging P23456 2 Purchase Order 2 30580 1/2/2012
A124 L02 Packaging P23456 2 On hand 2
A125 L03 Packaging P23456 2 On hand 2
A126 L04 Packaging P23456 2 Purchase Order 2 30580 1/4/2012
A123 L01 Customer P34567 2 Planned Order
Andrea M.
100% (1)Projects Completed
1
Freelancers worked with
1
Projects awarded
100%
Last project
3 Oct 2013
United States
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies