Which method should I use...?

[#1]

Hi

I have two tables;

     The first is an Order Header file and

     The second contains the Order Lines. 

The only piece of information I am interested in from the Order Header table is the  order status. 

What I want to do is only import data from the Order Lines table WHERE the Order Header order status = 5 or 6.

Table One

Order number	Order status	...
12345	5	...
23456	3	...
45678	6	...

Table Two

Item	Qty	Order no.	...
Plum	100	12345	...
Orange	200	12345	...
Apple	300	12345	...
Apple	400	23456	...
Grapes	500	23456	...
Peach	600	23456	...
Fig	700	45678	...
Peach	800	45678	...
Apple	900	45678	...

Desired Result

Item	Qty	Order no.	...
Plum	100	12345	...
Orange	200	12345	...
Apple	300	12345	...
Fig	700	45678	...
Peach	800	45678	...
Apple	900	45678	...

Because I have a one to many relationship and I don't want any info in the model from Table Two, I have no idea which method I should be using.

Thank you for your help.

Oli

[#2 · Accepted solution]

Hi,

I would do:

LOAD the first table in a temp table with the condition you want

LOAD the second table with the exists function : where exists([Order Number], [order No])

Drop the temp table

Fabrice

[sujeetsingh]

Load distinct and filter the data with conditions.

[Gysbert_Wassenaar]

Load all the data and add a listbox for Order status. Then simply select the order status values you're interested in.

[#5]

As I am still very new to this I have a question from a best practice point of view.

If load time is not a factor (due to over night schedule) should I go down the route of Aunez's method to avoid too much data in the model but at the expense of the load time?

Thank you all for you guidance.

Oli

[#6]

it depends on what the users want to see:

- everything (all Order status) : ListBox

- some part (because the other order status are meaningless): filter during the script

Fabrice

[Gysbert_Wassenaar]

Unless users shouldn't be allowed to see certain data at all or the amount of data causes performance problems, I'd say load all the data.