Relationship or Table problem, where am i going wrong ?

[hullstorage]

Hi all,

I have attached a sample copy of database as trying to get back in to access again and a bit rusty !

Can someone help with this, i am sure its simple but i am missing something and if you could explain lol

So if you look at goods inward this is fine !

The problem is i am trying to go in goods outward and when i create the shipment number etc i am trying to scan the su number and that to bring data in from the goods inward table as 1 shipment can have 1 item or 50 items all from the goods inward.

If you look at the goods inwards table i basically want the same for it the item is to be sent out but i keep getting duplicate error ?

Do i maybe need a query or is it something else as obviously its trying to duplicate the SU in the parts table

Thanks all

 

[CarlettoFed]

You must enter the value of the ShipmentNo field, from the GoodsOutward table, into the ShipmentNo field of the Parts table for all items that are to be shipped with that shipment.

 

[plog]

You shouldn't have your In/Out data in seperate tables, combine them into 1. That way you only have 2 tables for this--Parts and PartMovements. PartMovements would have this structure:

PartMovements
pm_ID, autonumber, primary key
pm_ExternalID, Short Text, replaces and will hold data currently in RMANumber and ShipmentNo fields
pm_Carrier, Short Text, replaces and will hold data currently in CarrierIn and Carrier fields
pm_Waybill, Short Text, replaces and will hold data currently in Waybillin and WaybillOut fields
pm_In, Yes/No, Yes=part is coming in, No=part is going out

That table can now hold all the data in both GoodsInward and GoodsOutward. Further, you don't have a quantity field, but it would go in PArtsMovements if it is needed.

 

[hullstorage]

You shouldn't have your In/Out data in seperate tables, combine them into 1. That way you only have 2 tables for this--Parts and PartMovements. PartMovements would have this structure:

PartMovements
pm_ID, autonumber, primary key
pm_ExternalID, Short Text, replaces and will hold data currently in RMANumber and ShipmentNo fields
pm_Carrier, Short Text, replaces and will hold data currently in CarrierIn and Carrier fields
pm_Waybill, Short Text, replaces and will hold data currently in Waybillin and WaybillOut fields
pm_In, Yes/No, Yes=part is coming in, No=part is going out

That table can now hold all the data in both GoodsInward and GoodsOutward. Further, you don't have a quantity field, but it would go in PArtsMovements if it is needed.

It's getting there but when i scan SU out it doesnt populate the part fields etc. ive attached if you could have a look please lol
I have attached database now and also a pdf that maybe explains it better hopefully lol

 

[plog]

First, you didn't build the Movements table correctly. You simply jammed all the fields from your 2 prior fields into 1 table. And now I see a PartID field has been added that was in niether the 2 original tables. Your losing me.

It's getting there but when i scan SU out it doesnt populate the part fields

What does an SU represent? How is it suppose to be tied to parts? Unless you have a table that tells what parts go to what SU number prior to scanning an SU number this will be impossible.

 

[hullstorage]

First, you didn't build the Movements table correctly. You simply jammed all the fields from your 2 prior fields into 1 table. And now I see a PartID field has been added that was in niether the 2 original tables. Your losing me.



What does an SU represent? How is it suppose to be tied to parts? Unless you have a table that tells what parts go to what SU number prior to scanning an SU number this will be impossible.

Ah right yea I will re do this.

The su number is like a licence plate for the part and we have a roll of stickers for these that we attach to all the parts out of the box and is very important as it seperates the parts so to speak as all the parts in the box could be same part number and not all the parts could have a serial number hence why they use them.
So in theory that is main part of it and each su is unique and never the same

Thanks again and I'll rebuild the movements when I get back in

 

[plog]

Let's make this more real.

You get a box of screws (Part #11), you add an SU number to every screw in that box (SU #3).
A day later you get another box of screws (Part #11), you add an SU number to every screw in that box--Will that be SU #3 or a new SU#?

 

[hullstorage]

Every single screw will have an su number but the box they come in will have an rma number. If I got 2 boxes of screws then there would be 2 rma numbers but every screw needs a su number

Rma 1 box of screws no 1 with 3 screws
Su1
Su2
Su3

Rma 2 box of screws no 2 with 4 screws
Su4
Su5
Su6
Su7

 

[hullstorage]

I've managed that bit before it's the actual sending out

So I create a box of mixed screws to send some where

Box of screws has a shipment no
And screws in the box can be
Su1
Su4
Su6

So rma on this part doesn't exist really

 

[plog]

Rma 1 box of screws no 1 with 3 screws
Su1
Su2
Su3

Rma 2 box of screws no 2 with 4 screws
Su4
Su5
Su6
Su7

The above represents 3 entities:

SUs - individual entities of parts, 1 individual screw = 1 SU

Parts - a class/type/grouping of an SU, 1 type of screw = 1 Part

RMA - a box/container of a Part which holds many Sus

Your Parts table is incorrect and doing the work of many tables. You need these tables:

Parts - this will hold only generic data about a part--PartNumber, Remarks. It is essentially a reference table.
RMAs - this will hold the RMA number and PartNumber
SUs - this will hold SU number and RMA number

Movements then holds only the Sus number along with the other fields I illustrated above.