Purchase Order and Goods Received

[Gazza2]

Sorry if this is in the wrong forum.

I need a little help with the design of the following.

I have a two tables setup like the so

TABLE 1 (TblDocument)

DocumentID (AutoNumber) - Primary key
DocumentNo
AccountID
DocumentType

TABLE 2 (TblTransactions)

TransactionID (AutoNumber) - Primary key
DocumentID - Foreign key
PartNo
OrderQty
ReceivedQty


I also have 2 Forms

FrmPurchaseOrder with FrmPOSub
FrmGoodsReceived with FrmGRSub

When I create a purchase order all goes well.

Now for the problem.

I create a purchase order for 10 * PartNo1 from Supplier1 on 20/8/12
Supplier1 delivers 5 * PartNo1 on 21/8/12
Supplier1 delivers 3 * PartNo1 on 22/8/12
Supplier1 delivers 2 * PartNo1 on 23/8/12

What I would like to happen is when I open the FrmGoodsReceived form it opens to a record that I have selected on a previous form ( Which I have working ) but if I open the form on the 21/8/12 and enter 5 in the ReceivedQty field then open the form on the 22/8/12 the 5 is still showing and if I enter 3 then the five disappears and so on for each time I receive.

What I would like it to do is create a new transaction for each time I receive a qty then in an unbound field at the end of the line i will have it show the qty outstanding when the form is opened and the RecievedQty field is blank.

Hope this makes sense and thanks in advance for any help.

Gareth

 

[jdraw]

I think you may get some ideas from this data model
http://www.databaseanswers.org/data_models/customers_and_partial_deliveries/index.htm

It seems to me you may have not considered Suppliers, Products... in your design if you have only 2 tables.

PS The business rules/facts associated with the data model can be found at
http://www.databaseanswers.org/data_models/customers_and_partial_deliveries/facts.htm

 

[Gazza2]

Thanks for the reply jdraw.

I have to apologise for not telling the whole story. I do have a products table and an accounts table( containing both supliers and customers ).

I have had a look at the data model and I think I have all of the tables but under different names as follows

Customers - TblAccounts
Addresses - TblAddresses
Products - TblProducts
Orders - TblDocuments
Order Items and Shipments - TblTransactions
Shipment Items - not quite sure how to implement this.

I have read the business rules page and I think the following line is what may be my problem -

"I have normalised the delivery and invoicing addresses, but I am not sure on
how to normalise the partial and full orders."

I have uploaded a copy of my db so you can see exactly what i have setup ( WARNING it is a bit messy but it is being used to work out the kinks (Like the above problem) before i create a proper one).

I hope I`m not being rude in asking you to have a look to see what my problem is (Apart from lack of knowledge)

Thanks
Gareth

 

[jdraw]

I have acc 2003 and can not read or open a database in accdb format.
I can only use mdb format.

 

[Gazza2]

Sorry jdraw here it is in mdb format

 

[jdraw]

Just saw your post and mdb.

I don't understand your tblAccounts with the yes/no fields.

Seems you are dealing with Customers and Suppliers as per the data model I referenced, and I think you have to identify these entities in their proper tables.

 

[Gazza2]

sorry jdraw

I should have said that some of the suppliers are customers or vice versa so I put them all into in table and had a yes no check box to say wether they are just suppliers or just customers or both.

I put them all in one table so i would not have duplicate data.

sorry if I didn`t explain myself properly

Thanks
Gareth

 

[jdraw]

In my view you now have confused who is what.
Customers are customers, Suppliers are suppliers identify your entities as such.
If you have a People Table, then use it twice and alias it a s Customers and Suppliers accordingly.

Putting everything in one table isn't necessarily preventing duplicate data.

Here's another model to reference
http://www.databaseanswers.org/data_models/customers_and_orders/index.htm

 

[Gazza2]

ok after implementing the changes you suggested I am still stuck on my original question so maybe if I put it a different way maybe it will make more sense to others rather than just myself.

To create a purchase order I open the main form and enter the supplier name(the document no and date are all created in the on open event) this creates a record in TblDocuments.

After i have entered the supplier name the focus moves to the subform which is a continuous form, I enter the part no and the quantity and this creates a record in TblTransactions and TblTransactionDetails.

When the goods arrive on the premises I then open up another form (FrmPOSelect) to select the correct purchase order this opens up FrmGoodsReceived with all the details of the purchase order.

What i would like to happen is another transaction to be created when I enter a number in the QtyIn textBox instead of just filling in the field in the table relating to the original transaction.

Hope that makes sense (cause it still doesnt to me)

Thanks for the help so far
Gareth

 

[jdraw]

Post your revised database in mdb format.

 

[Gazza2]

Here is the db

Thanks
Gareth

 

[NigelShaw]

Customers can also be suppliers and vice versa so it makes sense to have them in one table otherwise there would be duplicate data. I have both who are both at work...

Nigel

Sent from my OMNIA7 using Board Express

 

[Gazza2]

Thanks for the input NigelShaw.

Problem is I`m not sure which way to do it now.

Just reading up on normalization and I am more confused than ever so I will leave that decision till later once I have this problem sorted.

Thanks
Gareth

 

[Gazza2]

Ok after alot of reading about normalisation and data models I seem to be none the wiser about implementing my idea.

So my question is how do you handle receipt of goods from suppliers if the goods are sent in in multiple orders but they all relate to one purchase order that you have created?

It seems that it may be my wording that is causing a problem as a search on Google and Ask for "goods receive with microsoft access" only brings up my own posts, so if someone can give me a pointer of the correct wording for what I want to do it would be much appreciated.

Thanks
Gareth

 

[Gazza2]

Ok after alot of trial and error I have got it sort of working except this one bit.

I have now added two new tables ( TblPOHeader and TblPODetails), I have done this as I figured that a purchase order isnt actually a transaction until it is received which leads me to my problem.

I have two forms ( FrmGoodsReceived and FrmGRSub), now I can get all the details from TblPOHeader into FrmGoodsReceived which is linked to TblDocuments( I have done this by using dlookup for the fields I need to get the data for) but I am having trouble getting the data from TblPODetails into FrmGRSub.

FrmGRSub is a continuous form and if I use the following dlookup statement on the PartNo field :-

=DLookUp("PartNo","TblPODetails","PurchaseOrderID = " & me.TxtPODetailsNo)

I get a #name error which i am assuming I get because there is more than one Record relating to PurchaseOrderID.

PurchaseOrderID is the FK in TblPODetails
TxtPODetailsNo gets its data from the following dlookup statement :-

=DLookUp("PurchaseOrderID","TblPurchaseOrder","PONo =" & Forms!FrmGoodsReceived!TxtPO)

any help would be much appreciated

Thanks
Gareth

 

[jdraw]

So my question is how do you handle receipt of goods from suppliers if the goods are sent in in multiple orders but they all relate to one purchase order that you have created?

I think it would be best handled by breaking it down into logical steps:

1 - request item(s) from Supplier
2- receive item(s) from Supplier
3- verify received items vs requested
4 - add item(s) to Inventory
5 - Get Order from Customer
6 - Fulfill Order from Inventory
7 - Prepare Order for Shipment
8 - Ship Order to Customer

For your own benefit using this quote

multiple orders but they all relate to one purchase order

describe/define Order vs PurchaseOrder until you understand how they differ, if they do.

If I have misunderstood any of this, it is not intentional, and please clarify as necessary.

Your situation is classic -- until we understand the entities involved and how they relate, we are mainly guessing and can not offer a lot of focused advice. Another way to say this, if you can't define it, you sure can't build it.

 

[Gazza2]

Thanks for your patience jdraw.

That line should read:

So my question is how do you handle receipt of goods from suppliers if the goods are sent in in multiple Shipments but they all relate to one purchase order that you have created?

After a bit of reading I think I need to set a recordset on the sub form to pull the multiple records in the purchase order table into the continuous subform that is Bound to the transaction table.


I think from what I understand this will write a new transaction to the transaction table each time the form is opened and a quantity received is entered.

Thanks again

Gareth

 

[Gazza2]

Ok after reading a few bits on the internet I realised that you cant use Dlookup on a continuous form.

I have set up a query based on TblPODetails and have added the following Dlookup to the Primary Key Criteria field:

=DLookUp("PurchaseOrderID","TblPurchaseOrder","PONo = " & [Forms]![FrmGoodsReceived]![LinkedDocumentID])

Now if i open up the Query it asks me to enter the Parameter value for [Forms]![FrmGoodsReceived] and if i enter the number it opens the query to the correct records.

If i open the main form to the specified record it does not even show the subform and I dont know why.

Sorry to keep on about this but I just need to figure this out so I can carry on with the rest of the Db.

Thanks
Gareth