Relationship or Table problem, where am i going wrong ? (1 Viewer)

hullstorage

Registered User.
Local time
Today, 18:01
Joined
Jul 18, 2007
Messages
213
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
 

Attachments

  • Rico2021.accdb
    640 KB · Views: 131

CarlettoFed

Member
Local time
Today, 19:01
Joined
Jun 10, 2020
Messages
119
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.
 

Attachments

  • Dati.png
    Dati.png
    248.8 KB · Views: 136

plog

Banishment Pending
Local time
Today, 13:01
Joined
May 11, 2011
Messages
11,611
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

Registered User.
Local time
Today, 18:01
Joined
Jul 18, 2007
Messages
213
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
 

Attachments

  • Daily Procedure.pdf
    94.9 KB · Views: 179
  • Rico2021.accdb
    776 KB · Views: 146

plog

Banishment Pending
Local time
Today, 13:01
Joined
May 11, 2011
Messages
11,611
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

Registered User.
Local time
Today, 18:01
Joined
Jul 18, 2007
Messages
213
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

Banishment Pending
Local time
Today, 13:01
Joined
May 11, 2011
Messages
11,611
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

Registered User.
Local time
Today, 18:01
Joined
Jul 18, 2007
Messages
213
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

Registered User.
Local time
Today, 18:01
Joined
Jul 18, 2007
Messages
213
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

Banishment Pending
Local time
Today, 13:01
Joined
May 11, 2011
Messages
11,611
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.
 

Users who are viewing this thread

Top Bottom