Many To One?

[Sean]

Here's my situation:

I want to take a shipment of boxes going to one of our vendors and assign a
Shipment Number. This number is to be assigned to each of the boxes in the
shipment.

I have tblBoxes, tblVendors, and tblShipments.

How can I create tblShipments.ShipmentSerial.value AFTER I have created the
boxes, and assigned them to the specific. In other words, I can't create a
shipment number until I know I have a shipment to send and it has been
completed.

Thanks
Sean

 

[Sean]

Thanks, I have already got most of my database working. This is my one
remaining headache. I hope you can offer some more advice

If you are concerned
about not knowing the tblShipment.SerialNo at the time
you create the shipment or the box, don't worry. Simply
create a Shipment, attach boxes to it, and when you get
the serial number, enter it in the Shipments table and it
will be all set to go.

This is the part I am having difficulty with. I enter the boxes (for
example: 1, 2, & 3)

I want a form that creates a new shipment record when opened. After
Confirming from me that I want to ship the boxes it updates the SerialNo
based on the date and some other information.

I know how (and have set up the tables, queries, etc) to retrieve only the
boxes not yet shipped, etc; how to create the SerialNo through concatenating
the date and other information. I just cant get the form to create the new
record for me without some sort of direct input from me into a field in the
tblShipment.

Thanks again
Sean

 

[Bryan]

Sean,

I'm going to need to know the rough structure of the
tables you're dealing with. Could you put them down in
shorthand, much like I did in my previous post.

Bryan

 

[Sean]

Sure,

tblVendors
------------------
VendorID PK
VendorName Text

tblShipment
----------------------
ShipmentID PK
VendorID FK
ShipmentSerial long int
DateShipped Date
DateUploaded Date
DateReturned Date

tblBoxes
------------------------
BoxID PK
VendorID FK
ShipmentID FK
BoxSerial long int
DateCreated Date
Region Byte
Box Byte
Start long int
End long int
Records long int
Assigned Yes/No
Shipped Yes/No
Returned Yes/No
Completed Yes/No

In case your wondering why I included VendorID in tblBoxes... The users have
to assign boxes to vendors BEFORE a shipment is created. If I am wrong in
my assumption that it is necessary to include the FK in tblBoxes without
information entered in the "middle-man" (tblShipment) please correct me.

Our system is completely turned around but maybe this analogy of the "why" I
have to go from Boxes to Vendors to Shipment will help:...
(sorry for the Kindergarten story, but I figure simple is best... right?)


Bob makes paint. Bob makes paint out of material brought to him through the
day; therefore, Bob has no idea what colors he will make until the material
gets there. Bob has his employees make the paint. Then, he calls his
buyers and tells them what he has to offer that day. One wants red and blue,
the other doesn't want any today. Bob has his employees put the red and
blue paint in a corner for the buyer. At the end of the day Bob puts all
the paint from the corner on a truck for the buyer. The end.

Thanks again for your help. If I could just get this problem licked I will
be one happy camper.

Sean