Combining a Unique Record in one table to several related records in another table.

[wchelly]

This is a piggy-back question on this thread for a query rather than a table
http://www.access-programmers.co.uk/forums/showthread.php?t=227357

If I have the following tables

Shipments_Table
ShipperID (Unique)
Shipment_Number
Departure_Date
Arrival_Date
Package_Type
Number_of_Packages

And this table

ShipmentPackages
ID (Unique)
ShipperID
PackageID

And this one

Packages
PackageID(Unique)
Comment


I want to make this query

[Shipments_Table].ShipperID
[Shipments_Table].Shipment_Number
[Shipments_Table].Departure_Date
[Shipments_Table].Arrival_Date
[Shipments_Table].Package_Type
[Shipments_Table].Number_of_Packages
[ShipmentPackages].ID
[ShipmentPackages].ShipperID (ShipperID can be repeated….could be 100 or more)
[ShipmentPackages].PackageID (These must be unique per Departure Date AND PER Shipment, but can include a range of 1-150 or so)

I can’t figure out how to make One ShipperID….combine with multiple [ShipmentPackages].ID’s all in one query.
What it does is combines One ShipperID and One [ShipmentPackages].ID and it leaves off all the rest.

 

[wchelly]

Essentially, I want to make a table that looks like this

ShipperID 800
Shipment# WEF_045
Departure Date 7/1/2012
# of Packages 24

PackageID's

ww-001, ww-021, ww-035, ww-094,
ww-009, ww-029, ww-039, ww-091,
ww-008, ww-028, ww-038, ww-092,
ww-006, ww-022, ww-032, ww-099,
...etc (include all 24 package ID's)

 

[spikepl]

Do you? What is this to be used for?
This is not excel so you cannot make a table where the columns contain different things in different rows.

You could make a table with one text column, and construct each line, but what for?

If this is just for paper output then make a report with a subreport with multiple columns, and you can get what you want. How ? Find some online tutorial on reports.

 

[wchelly]

Spikepl,
You are correct, I do not want to make a table that looks like that. But I do want to make a temporary table that lists everything for the shipment in one row, because I want to generate a form to which data can be added. Data in this table would be deleted after a week or so (after we know the form will not need to be regenerated). It is somewhat complicated, but I think I know what I need. The form must be editable, and to do that all the data needs to be in one table....but for the long term, the data can stay in the related tables.

 

[spikepl]

Forms are the vehicle for interaction with data, and can be shaped and formed in whichever way necessary. That does not mean that a table has to look the same way as a form.

Storage of data in Access is NOT related to display. And display does not dictate how data is stored. Combinining data is accomplished using queries and that data is displayed in forms/subforms, not by creating new tables. If you create a new editable table, then you have to feed any changes back to the original data, and that is simply redundant work.

 

[wchelly]

When I use multiple tables in a query, sometimes it will not let me edit the data unless it is in one table. And this is part of the problem

The other part, is that I have shipment data in one table, and package data in another table (which is really a subset of the shipment data), and I can't figure out how to display multiple packages on a page with the rest of the shipment data so that it can all be viewed (and edited) on the same page.

One additional piece of information....
I really need to print this form ONE time only. There are fields on the form that do not need to be stored permanantly. My thought was just to create a temporary table for this data, append all the other needed fields that are already stored to the same table, and then autodelete after 7 days.

 

[spikepl]

Not all queries are updatable. Google allen browne updateable.

Your data consists of shipment data and shipment details - one-to-many. This type of data is normally handled by a form showing shipment info) having a subform showing the shipment details - a list of items here, I guess. All this would be editable without any coding.
This is similar to order/orderdetails, and the web and AWF and alos Access documentation are full of examples. Download some database and see how it works. Google Access templates - microsoft has a whole plethora of them. Google form/subform tutorial.

 

[wchelly]

OK, thanks! Having the subform selects the correct data, (this is the wrong forum for this question, I know) but since this thread was started here...is it possible to make the subform look like it is just integrated into the form so that it doesn't look so intrusive if it's printed. Or should I just create a report for printing instead of trying to print a form?