Suggest Access relationship query or anything

[mnaem02]

Dear Experts i hope you are fine



i am stuck in the access problem from 1 wee and unable to find how i can move up.
I want to make Purchase order(PO) form (to buy products from suppliers) which i have done , now i am making good receive note(GRN) which is receive in partial quantity e.g i order 20 computers but received 5 now (GRN will raised) after 20 days i will receive 6 ,against same PO ) than GRN of different number will be raised
In Attached file for GRNEntry1 which i have made from GRN_Entry table and GRNEntry query (PODetail and GRN detail) but i think i cannot update in value QTYRec fromsubform.

if i can add on QTYRec than everything will be fine

please any solution for this pls

i tried this web but still not databaseanswers.org please if u can rectify and email
mnaem02@gmail.com

pls

[orange]

Here's a draft model that may be helpful. I am not familiar with the details of your business.

Good luck

[mnaem02]

Thank you

but still no luck, business retail business.
i just want partial GRN , in attached file with reference to above chart my tablr "PODetail" = "OrderDetail" and GRNDetail = Received.
please see my attachment and suggest me where i am wrong, as i mentioned i hav seen so many charts but unable to to it on my database

Please look my sheet and suggest

[mnaem02]

please help

[orange]

I created the draft model based on your post. I named the tables/entities based on my understanding.
Received is similar to your GRN. I admit to not knowing details of your business/requirements.

In Received you can relate
--ProductID
--QtyReceived
--ReceivedDate

to Order/OrderDetail
-ProductId
-OrderId
-QtyOrdered

Please describe exactly what you need---be specific with a few samples.

[mnaem02]

thnx
Business Retail, general stores. or grocery stores

example i ordered 100 computers ,20 harddisk and order no. will be (0001) than i will received goods on date 1-jan-2017 only 50 computers and 10 harddisk, i will be book on access FORUM (50 computer & 10 hardisk) with Receive no. will b (0001)
than again on 15- jan-2017 i will received against same po no (0001) 10 harddisk and 20 computer and when i will book i will get received no.( 0002) similarly remaining 50 computer on 20-Jan-2017 received no (0003)

(one po have 3 received no. and on received no can have so many products)

and i also want all information of order table in the received table so next i will process for payment FORM base on the receive ( during payment booking all info in the order no . like product description, size etc)

[orange]

No, that's not what I mean. The model doesn't necessarily represent what you need!

Create a scenario with some test data. Start with a clear description and add some data values. Let's see in detail(with an example)
what you data looks like and what you need.
And adjust the model until we get it to meet your requirement.
That is, make sure you can get what you need from the data structure in the model.

So build some sample data for each ( a few records is all that is required)

Supplier

Order

OrderDetail

Product

GRN/Received.

[mnaem02]

I have attached file with all table which you said relationship as per above relationship structure except received relationship i am unable to do.
after that relationship i want to raise order and want to received goods in partial.

In attachment test001 order not enter and in test002 i have entered order now i want to received 20 laptops 20 computers in 1st order

thanks

[orange]

??? I do not understand your post.???
I would like you to describe the business to be supported by this proposed database.
If English is not your native language, then write a description in your language and use Google translate to get an English version.

Who is the Supplier? Who is the Customer?

Let me suggest a description of a situation, and you modify it to make it fit your business.

This is a mock up of a requirement. Adjust to fit your situation. If it's too detailed, adjust as needed.

You are a business that uses computer equipment. You purchase equipment from more than 1 Supplier. You are making a Purchase Order to
buy 20 laptop computers from supplier ACME Inc on Sept 30, 2017. Each Computer costs $500. The total value of the PO is 20*500 = $10,000. The Order is going to be delivered in parts --3 parts to be exact. The 3 deliveries will be on Oct 12, Oct 19 and Oct 26 2017.
The intention is to deliver 10 on Oct 12 2017; 4 on Oct 19 and 6 on Oct 26 2017. You want to verify the Qty on each Delivery, and to ensure that the Qty Received after the Oct 26 2017 delivery matches the Qty Ordered.



Note: 11:12AM I did not see the last part of your post #6 earlier.
All I saw and responded to was

Business Retail, general stores. or grocery stores

I have to go out for a few hours, but will check back this evening.

You may get more ideas from this data model from Databaseanswers.org
Here are the related business facts.

[mnaem02]

You are a business that uses computer equipment. You purchase equipment from more than 1 Supplier and 1 supplier has particular products e.g 1 supplier providing computer and laptop only keybord provided by other . You are making a Purchase Order to
buy 20 laptop computers 30 harddisks from supplier ACME Inc on Sept 30, 2017. Each Computer costs $500 and hardisk cost $10. The total value of the PO is 20*500 + 30×10 = $10,300. The Order is going to be delivered in parts --first delivery 5 laptop and 10 hardisk on 10 october 2017 and 2nd delivery of 5 laptop and 10 hardisk on 20 october 2017 .i want to book qty received want to ensure it matches the Qty Ordered. (Qty receive number should be same for 1st delivery of laptop and hardisk. Moreover with quantity receive number i will book payments which i will make later and when i recall receive number on payment booking it will show all detail like supplier name product price quntity.. i hope now you will understand

[mnaem02]

Sheet i have attached on that order part is correct but in receiving part i am having problem. Even as per relationship chart first you have posted i cannot join order detail with receives on one to many relation. 1 order have so many receiving.

[orange]

Hmmm? I've tried modelling based on your latest info and offer this diagram below. I hope it's helpful.

I tried to generate a database from the model, but have had no luck.



Usually with a delivery, you get a bill of lading that represents "all items in the box(es)". You can compare that (if you have one) and the physical items in the delivery and record same in your GoodsReceived table. You can query and compare OrderDetails for an Order and GoodsReceived for that Order to see if items and quantities match and reflect Total goods ordered.

Good luck with your project.

Update: 9:30 PM

I did get the model software to gen a database. It added an entity (6) to resolve something. I added some data to your tables and created a few queries to get/select some results to view. I did not add any Forms etc.

I added some data to tables and created queries to show that:
-not all Ordered Products have been Received.
-Partial Deliveries.
-ProductsOrdered vs ProductsDelivered
-ProductsDelivered vs ProductsReceived.

I'm attaching a zip of the database for demo/test (GRN.zip). I'm not real satisfied with the generated database(GRN9.accdb). It may give you some ideas. To do some of the calculations you want will require considerable forms and coding (in my view).
Bottom line is you have to get a model of the business process(es) and make sure you can get the data you need from the data structure. Then refine the model as needed, then build your database, then forms/queries etc.

Good luck.

[mnaem02]

thank you sooooo much i am trying understand the file and chart , can u tell me the web for charts