linking tables

[need help]

Hi All

I want to join two tables.

I have a table [Freight Detail] table where I use forms to enter details of
freight (cartons, pallets etc). I have also created another table [Freight
Invoice] where I want to record the details of the invoice from Transport
Company that I receive later (which has not data at the moment).

[Freight Details] [Freight Invoice]

freight invoice freight invoice
cartons invoice #
pallets invoice date
con note (main reference)etc
cost etc
etc

What I want to do is to be able to relate these two table so that I can make
Freight Detail table subform of Freight Invoice. So when I get a transport co
invoice, I want to be able to pull up all the related data from a Freight
Detail table and apply the costs.

I am having trouble because I enter the [Freight Detail] first and the
[Freight Invoice] later (when I receive the actual invoice). So there is
already data in the [Freight Detial] table but nothing in the [Freight
Invoice].

One [Freight Invoice] can have multiple connote

It almost seems like a one-to-may relationship in the reverse

 

[Ryan]

In order to have a one to many relationship, the one must be there before you
add the many. If you will always have a Freight Detail first, I would make
that be the one side of the relationship and the main form, and then make the
Freight Invoice be the many side of the relationship and the subform. If you
must have the Freight Invoice be your main form, then you will have to create
seperate query and form that shows all unasigned Freight details and add the
Freight Invoice Number to the Freight Details table with some kind of
dropdown or checkbox.

 

[pietlinden]

Hi All

I want to join two tables.

I have a table [Freight Detail] table where I use forms to enter details of
freight (cartons, pallets etc). I have also created another table [Freight
Invoice] where I want to record the details of the invoice from Transport
Company that I receive later (which has not data at the moment).

[Freight Details]      [Freight Invoice]      

freight invoice       freight invoice
cartons                    invoice #
pallets                     invoice date
con note   (main reference)etc
cost  etc
etc

What I want to do is to be able to relate these two table so that I can make
Freight Detail table subform of Freight Invoice. So when I get a transport co
invoice, I want to be able to pull up all the related data from a Freight
Detail table and apply the costs.

I am having trouble because I enter the [Freight Detail] first and the
[Freight Invoice] later (when I receive the actual invoice). So there is
already data in the [Freight Detial] table but nothing in the [Freight
Invoice].

One [Freight Invoice] can have multiple connote

It almost seems like a one-to-may relationship in the reverse

No, it's not. Invoice is the main form, Detail is the subform.
"Freight Detail" child table contains InvoiceNo, which joins it to the
parent table, Freight Invoice. Do NOT make InvoiceNo in "Freight
Detail" a primary key. Just leave it as is. Then join the two tables
in the relationships window. Then create your subform based on
Freight Detail and drop it into the Main form, based on "Freight
Invoice".

 

[need help]

Thanks guys for all your help but I forgot to mention couple of things.

The [Freight Detail] is already a child/subform of another table.

So [Order] --> [Freight Detail] <-- [Freight Invoice]

Order # Order #
F Invoice F Invoice

So [Freight Detail] is a sub form of two seprate forms.

Innitially, I use the 'Order' Form with 'Freight Detail' subform to enter
data.

And what I want to do is to have another form 'Freight Invoice' and make
the 'Freight Detail' subform and apply relevent existing data from [Freight
Detail] to the [Freight Invoice].

When I enter data via the 'Freight Invoice' Form it come up wtih message
something like " Can not change record becasue related Recrod is required in
'Order'".

Thanks for your help