UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Looking For Best Advice On Creating Tables And Relationships With This Please, Access 2016    
 
   
hullstorage
post Feb 13 2020, 11:25 AM
Post#1



Posts: 283
Joined: 7-October 08



Hi all,

trying to create new database but got a bit rusty lol

This is just for keeping records of customers items we store for them



Goods Inward Table

Arrival_Date (when delivered to site)
Order_Number (unique number but could have many items with different su numbers, product number and serial numbers)
Carrier/Courier (this could be dhl, ups or just a one off courier)
CarrierTrackingNumber (not Req always)


Goods Inward Details Table
SU_Number (each item will have su number)
ProductNumber
SerialNumber
Location (stock where it will be placed)

Locations Table
LocationNumber (location inwarehouse)


Goods Outward Table (this is when they ask us to remove from stock and send out to customer on a carrier)
(note rma number will not be used when despatched)

SU_Number
ProductNumber
SerialNumber
DateOut (despatch date)
Carrier/Courier
CarrierTrackingNumber


example of goods inward -
so a carrier/courier may bring in 5 items which all have the same ORDER NUMBER and TRACKING NUMBER
but each items will have ProductNumber(not unique), SerialNumber(sometimes can be blank n/a all the time) and SU number (unique)
then they will scanned in to each location which can be various locations

Goods outward
we would scan the su number
select carrier
and scan waybill number
Go to the top of the page
 
tina t
post Feb 13 2020, 02:58 PM
Post#2



Posts: 6,370
Joined: 11-November 10
From: SoCal, USA


QUOTE
This is just for keeping records of customers items we store for them

i don't see any mention of a tblCustomers.

also, what are the relationships between tables?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
hullstorage
post Feb 14 2020, 04:03 AM
Post#3



Posts: 283
Joined: 7-October 08



this is for 1 customer only so thats not needed on this occasion as its simply for them only ?

I suppose the relationship will be the SU number or RMA but as we dont use RMA on goods outward then maybe just SU

All items delivered must have SU label attached then we scan SU, Part Number and Serial ?

If customer asks for info they normally ask to search SU ?

So DHL brings in say 5 boxes today, we first go on goods inward and then attached a SU label to every box (these are unique on every item in the warehouse)
Then so these 5 boxes will probably have the same RMA (order Number) on each item ?

So we scan the RMA, then waybill barcode, Su Label, Part No, serial and then the bay number ?

So trying to say scan rma number, waybill then a subform to scan su number, part number, serial and then bay no
Go to the top of the page
 
hullstorage
post Feb 14 2020, 05:26 AM
Post#4



Posts: 283
Joined: 7-October 08



scan inwards daily procedure

carriers from dhl arrives

scan rma number on box (this is the unique order number which can contain 1 part or multiple parts)
scan waybill number from delivery which is the same for every item

attached su number label(s) (unique su number for every box)
Scan su number
scan part number
scan serial number
scan to location

if more than 1 part then we repeat this
attached su number label(s) to every box (unique number for every box)
Scan su number
scan part number
scan serial number
scan to location

so we dont want to keep scanning rma number and waybill if there is more than 1 part



So then say ups arrives with 2 boxes
this will then be a different rma and waybill

if more than 1 part then we repeat this
attached su number label(s) to every box (unique number for every box)
Scan su number
scan part number
scan serial number
scan to location
Go to the top of the page
 
hullstorage
post Feb 14 2020, 05:55 AM
Post#5



Posts: 283
Joined: 7-October 08



THIS IS WHAT FORM WOULD ROUGHLY LOOK LIKE
Attached File(s)
Attached File  Annotation_2020_02_14_105456.png ( 21.48K )Number of downloads: 5
 
Go to the top of the page
 
tina t
post Feb 14 2020, 03:00 PM
Post#6



Posts: 6,370
Joined: 11-November 10
From: SoCal, USA


well, generally speaking, i would be asking some questions to clarify relationships between tables. such as

so the SU number is unique to every box, correct? can one box contain more than one part number? more than one serial number?

what about quantities? does each box contain only one part, or more? if multiple pcs, will you always send out the same quantity that you received?

do you need to account for each shipment, and all parts received, from when you take them in, to when you send them back out? if so, should there be a relationship between goods in records, and goods out records?

no need to answer me. answer yourself; make sure you fully understand the business process. it's actually very common for a person who follows a business process in his/her daily work, to not know all the details of the process.

then you need to fully define the relationships between the entities. such as

one shipment may have many boxes AND each box belongs to one shipment.

QUOTE
this is for 1 customer only so thats not needed on this occasion as its simply for them only ?

as for having only one customer... well, if it's absolutely impossible that you could ever have more than one customer, then perhaps not. but i very very rarely say never when it comes to a business process. so if the absolutely impossible happens, you would have to alter the structure of the db and the user app to accommodate that. i would probably build for multiple customers, because it would be easy enough to incorporate that as you go - easier than adding it in later. but that's a business decision that you have to make.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th February 2020 - 02:42 PM