transfer

[mis]

Hi,

I have a problem in my database I have a transfer table which requires two fields one to say the store the shipment is from and the other the store destination.

I have a store table where the addresses etc are stored but access want let me put storeid in the transfer table twice any idears. ??

 

[Len Boorman]

believe that problem is because you need to be able to handle regular shipments in that these shipments come from a particular source and go to a particular destination on repeated occassions.

Your table is probaly set up such that from and to ID's form a unique key or index.

Suggest that you need certainly another field.

First additional field could be a Date field. Date of transfer maybe. Make this part of the unique identifier. So a shipment from a source to a destination can be repeated but not on the same day. Maybe a shiment reference instead of date would be appropriate.

Len

 

[mis]

thanks

Thank you for replying the transfer table/s already hold a date field,

Tables

tblTransfer
TransferId
Date
ShippedDate
FromStoreId
ToStoreId

tblTransdetail
TransferId
ProductId
Qty

that is the current set up the fromstoreId and tostoreId fields i would like to use the storeId as the business only transfers stock between stores and so all information is store in this tblstore table, where storeID is the primary key.

 

[Len Boorman]

for tblTransfer

What is primary key
Are there any Indexes

Is there a relationship set between tblStore and tblTransfer. If so Exactly what type of relationship

Len

 

[mis]

Not sure

Hi,

The store table is used to display the destination address and the sent address for each transfer taken place.

I think i could change the table so that the destination address is writen in mannually with adding

DName
DAddress
DTown
DCounty
DPostCode

this would mean extra work and it would be easier for the user to select the name and the information be automatically retrieved.

 

[Len Boorman]

for tblTransfer

What is primary key
Are there any Indexes

Is there a relationship set between tblStore and tblTransfer. If so Exactly what type of relationship

Len

To establish why you cannot add the storeID twice in tblTransfer I need the details of the tblTransfer. If I am not making myself clear then just tell me and I will try to explain another way.

I suspect that
the tblTransfer has a 1:1 relationship with tblStore or
the tblTransfer has a Unique Index set on "to" or "From" StoreID's
the tblTransfer has a primary key of "to" and "from" StoreID

Generally these are all situations that could prevent yoy duplicating an entry

Len

 

[mis]

Understand

I dont really understand, yet at the moment there is no relationship as the table will not let me place two storeId fields into the table when designing it so i have left it out untill i find out why any good.

 

[Len Boorman]

You should be able to add fields of the same data type into a table in design view. The names must be different

I will send you a Private Message with my email address. Send me a copy of the database and I will have a look

Len