Prefix + AutoNumber as Item Code

Model.jpg

I'm back! The image above shows my progress. But I've only just made tables. I intend to create queries that I ought to include in my relationships model but they could follow later. I wanted to show my model first, plus I'm also stuck.

PS: As property custodian, I am in-charge only of receiving items delivered from suppliers as we have a Procurement department who's in-charge of supplies requisition. All the forms being used in our organization have duplicates for: the end-user, finance, procurement, and admin (property custodian).

Before you guys jump to conclusions, let me explain the process:

1. Every department has different donor sources (sources of funds). That means, all the supplies that employees from a certain department will request, should be charged to their respective funds.

2. An employee requests for supplies using the Supplies Request (SR) form IF I don't have stocks in my inventory. I've used Gina's model as reference. I copied a few bits and modified it to suit our needs. I hope you don't mind Gina. :)

3. The requester gives the SR Form to Procurement. Procurement makes a Purchase Order (PO) for the items. One SR could have different POs because there are different vendors for different items.

4. Procurement orders items from supplier/s.

5. Supplier/s deliver the items ordered and presents a Delivery Receipt (DR) or Charge Invoice. I, property custodian, makes a Receiving Report (RR) to signify the receipt of items.

6. Upod making the RR, I want the items to be automatically reflected in my inventory.

7. I now have stocks in my inventory. If an employee requests for an item from my stocks, I let him/her sign an Acknowledgement Form which should serve as my reference for releasing stocks.

PROBLEMS:

1. I'm stuck at POs and RRs. I don't know how to relate the SRs to the POs, and the POs to the RRs, and the RRs to the Inventory. :(

2. Are my relationships correct for the other tables?

That's all for now. I'm not quite done yet. There's another table I have to make for staff requesting items on stock. But I wanted to clear the problems first before I move on.


HELP. :banghead:
 
Last edited:
Okay, this is going to take more than a minute to go thru so don't think I forgot you just a lot to digest.
 
GinaWhipp said:
Okay, this is going to take more than a minute to go thru so don't think I forgot you just a lot to digest.
Click to expand...

Haha. Yeah. I would really appreciate it if you could give me feedback about this. :) Thank you in advance. :D
 
First, let me say this is MUCH better! :D

Don't mine you *stealing* from me that's what those Data Models are for. Glad you found them useful!

In going over this I think we should one bit at a time so as not to confuse ourselves. But first I would like to confirm the steps. And remember details not required just steps...

1. Get Request Form
2. Fill out Purchase Order
3. Receive items
4. Acknowledge Receipt

Are those the steps?
 
GinaWhipp said:
First, let me say this is MUCH better! :D

Don't mine you *stealing* from me that's what those Data Models are for. Glad you found them useful!

In going over this I think we should one bit at a time so as not to confuse ourselves. But first I would like to confirm the steps. And remember details not required just steps...

1. Get Request Form
2. Fill out Purchase Order
3. Receive items
4. Acknowledge Receipt

Are those the steps?
Click to expand...

1. Get Request Form (Supplies Reqeust)
2. Fill out Purchase Order
3. Receive items (Receiving Report)
4. Acknowledge Receipt (In-stock item requisition)

Yep, I guess that's it. There are more steps I should add but I'm focusing on this for now. Those could follow later on. It's really kind of a long process.
 
Last edited:
Hmm, okay because the t_SupplierRequestDetail is the same as the t_PurchaseOrderDetail there really is no need for two you can use the t_SupplierRequestDetail for both. So, the question is...

Is there one Purchase Order per Supplier Request? The answer will help determine which table the PurchaseOrderID goes in.
 
Isn't the Acknowledgement Form for when the Department receives the items? If so, that needs to be attached to the Purchase Order.

Hmm, the Donor, aren't they attached to the Department? And since Department are assigned to Employees then you would only need to link to Department.

Okay, will wait for answers before going any further.
 
GinaWhipp said:
Hmm, okay because the t_SupplierRequestDetail is the same as the t_PurchaseOrderDetail there really is no need for two you can use the t_SupplierRequestDetail for both. So, the question is...

Is there one Purchase Order per Supplier Request? The answer will help determine which table the PurchaseOrderID goes in.
Click to expand...


Oh yeah. I see your point.

The answer to your question is no. There could be more than one Purchase Order per Supplies Request. And there could be more than one Receiving Report per Purchase Order. :)
 
GinaWhipp said:
Isn't the Acknowledgement Form for when the Department receives the items? If so, that needs to be attached to the Purchase Order.

Hmm, the Donor, aren't they attached to the Department? And since Department are assigned to Employees then you would only need to link to Department.

Okay, will wait for answers before going any further.
Click to expand...

First question:

Isn't the Acknowledgement Form for when the Department receives the items? If so, that needs to be attached to the Purchase Order.

Yes, but no.

For better visualization, I will divide items to three categories.

  1. 1. Office Supplies (Consumables)
    • All consumable supplies are kept in my inventory and custody.
    • These are the items that need to be logged in the Acknowledgement Form.
    • The requester for these items will always be from Admin. The receiver of the items will be me.
  2. 2. Items that need Property Accountability
    • There are specific items that when issued, need to be given Property Accountability. So there's a Property Accountability Form for that.
    • Items could be passed on from one employee to another but it has to go through me for proper turnover.
  3. 3. Items that don't need property accountability and can be given directly to requester.
    • This is the easiest. There is no acknowledgement form. The Supplies Request Form has a "Released by" and "Received by" fields. This serves as the acknowledgement form.
    • Once items are delivered and I receive them, I will look for the SR and PO, make an RR, mark items as received. Then I could deliver the items to the requester and let them sign the SR for acknowledgement. :)

Oh my gosh, I hope I was able to explain things well. It's so complicated. :banghead:
 
Okay, let me digest this and I will look at the Model, amybe not finishing tonight... thunderstorm warning AGAIN! :banghead: Ugh, so sick of this weather...
 
GinaWhipp said:
Okay, let me digest this and I will look at the Model, amybe not finishing tonight... thunderstorm warning AGAIN! :banghead: Ugh, so sick of this weather...
Click to expand...


No problem! :)

I'm actually making a new data model just so I have more options.
 
Gina hasn't been online for a while. I think she's taking a well deserved break, so if she or someone else doesn't reply in a few days, bump the thread again and I'll take a look.
 
So sorry for delay :o Grandkids wiped me out (youngest one is almost a year)! I am now looking at your database and will have an answer shortly.
 
Oh I'm sorry. I didn't want to sound demanding or anything. :)

Anyway, I do have another question though. I want to see my inventory levels real time. Would it be wise to do a calculated field on a table? If not, how then should I do it?
 
A couple of questions and I can upload what I have done...

1. Who acknowledges the receipt of goods?
2. What is the t_DSPN table for? Is that just for Donor information?
 
GinaWhipp said:
A couple of questions and I can upload what I have done...

1. Who acknowledges the receipt of goods?
2. What is the t_DSPN table for? Is that just for Donor information?
Click to expand...

I will read the link you said. :)

As for your questions,

1. The employees/requester are the ones who acknowledged the receipt of goods everytime they ask for a certain item from me.
2. The t_DSPN is for Donor Information and so I could have a source for the donor code (DSPN) to link it to Department. Each department can have several DSPNs though.
 
GinaWhipp said:
A couple of questions and I can upload what I have done...

1. Who acknowledges the receipt of goods?
2. What is the t_DSPN table for? Is that just for Donor information?
Click to expand...

It's so complicated. :( Can I not calculate on hand stocks using a query or report? :(
 
You must log in or register to reply here.

Similar threads

T
Solved Autonumbering in tables
Replies
17
Views
974
Pat Hartman
P
J
Show parent child linked records via VBA
Replies
9
Views
428
jinsatoemo
J
H
Beginner Seeking Help: Building an Access Database for Volunteer Fire Department
Replies
9
Views
470
arnelgp
arnelgp
W
Solved Duplicate Items in Query
Replies
5
Views
892
wmix
W
J
code to close my current form on pressing the button to open my Main Menu
2
Replies
20
Views
677
Edgar_
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom