How to select individual records from different tables to populate report?

[whimbrel]

I have what I think is a complex problem and no idea how to solve it as I am a novice in this area. Any help would be greatly appreciated as a have a deadline and time is running out.

I require to be able to print out a carrier schedule report on a daily basis. This consists of up to 4 addresses and a consignment number in a specific layout on the printed page.The addresses are held in an address table and the consignment numbers are held in a consignment number table. Both tables have autonumber key fields.

So I need to be able to select 4 individual address records from the address table for use in the report.

I need to be able to select one consignment number from the consignment number table for use in the report.

I tried to use the report wizard but there is no obvious relationship between these tables that I can create.The consignment numbers are for one use only and a different one needs to be used each day.

So I need a mechanism to establish a relationship between the selected addresses and the selected consignment number and also to flag up that a consignment number has been used, by somehow using specific fields in the consignment number and address tables presumably.

I appreciate that there's several facets to this application but I would be grateful if anyone can give advice on any facet of the problem.

Thanks in anticipation.

 

[Rabbie]

I think we need a little more info to help you with this. Is there a field in the consignment table that points to the address table?

If so you can write a query that links the tables and use that to write the report.

It would be helpful if you could let us know some detail about these tables.

 

[whimbrel]

Additional information

I have attached simplified examples of the database and the carrier schedule report.

I added the Premises 1, Premises 2, Premises 3, and Premises 4 Yes/No fields with the intention of somehow using fields these to get the addresses for the schedule but they aren't necessary. I just need a mechanism to be able to select 4 different premises to go on the schedule.

Similarly I added the Used and Date Used fields to the Consignment Number table with the intention of using these to flag up which numbers could and could'nt be used.

Up to 4 premises can be on the carrier schedule and the consignment number is printed under each address and at the top of the report as shown.

There is currently no field in the consignment table that points to the address table.

The consignment number is allocated to the schedule on the day the report is run. Each consignment number is for one use only i.e. having been used for a schedule it can't be used again.

Thanks, Rabbie, for having a look at this.
whimbrel