Search a value in multiple tables

[CIIIHAN]

Hi!

I have 2 tables with identical fields.
the first one, Orders, contains.. well orders.
these orders can be split up, if the batch is to big to produce it in one day.
when split 2 records are added to the second table, Splitorders.

all orders across the 2 tables have unique IDs called SplitID

Is it possible to find in which table a specific SplitID is?

Thanks in advanced

 

[spikepl]

I have 2 tables with identical fields.

Why? That is denormalized data, and it causes a problem, as you've just found. Keep all in one table and add a column so you can mark which is which.

 

[CIIIHAN]

Why? That is denormalized data, and it causes a problem, as you've just found. Keep all in one table and add a column so you can mark which is which.

Its for the sake of different departments here.
Splitting up orders is just for the planning departmant while is will only clutter the data for accounting.

Say we get an order of 10000kg of icing.
It is impossible to finish this in one day
So the order needs to be split up in to say 5 bits of 2000kg.
When all 5 bits are done accounting should just confirm 1 order, the original of 10000kg.
I hope this makes sense.

 

[spikepl]

for the sake of different departments

Not a valid reason to contravene normalization - data can be filtered, especially in queries.

clutter the data

Users have no direct access to tables, so data shown will be theirs only. "clutter" is no argument for invisible data. For access, with proper indexing , it makes no difference.

So again, like data goes into the same container.

 

[CIIIHAN]

Thanks for your reply.
I guess I'm not that good with Access.
I know how to normalize and stuff yet after alot of pondering on how to solve this problem this was the solution I thought would be right.

I know I would be asking alot but, would you like to help me optimize what I'm working on?
If you are intrested I explained what I'm working on in an earlier thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=281470

Thanks in advanced

 

[jdraw]

A couple of points.

First I agree with spike, you should get your data structures designed and tested to support your business rules using standard techniques (normalization).
Second, I responded to your earlier post and it seemed you had solved your issue.

?? Is this a new problem??

If you want to search across multiple fields in multiple tables see this post and sample database.

But I recommend you adjust your design sooner rather than later and get your tables and relationships aligned with your requirements.

 

[CIIIHAN]

Thanks jdraw.
The previous issue has been solved, this is a new problem.
I'll look into the link you send.

I understand your worries about my tables. Yet I can't come up with another solution.
I just cannot wrap my head arround it. Would you mind helping me out on this topic?

How would you do the splitting of orders?

To give a better perspective I'll go a little more into detail.
As stated before, when orders are too big for one go, they need to be splitted.
I've added a column to the orders table to see if the order is splitted or not
Each record in the orders table has an unique OrderID(PK)
But when the orders are split the OrderID will be duplicated in the splitorders table.
Thats why I added SplitID, SplitID is the primary key for splitorders table.

When viewed in the form both orders from the orders table and the splitorders table need to be shown.
To have one identifier for all records across both tables I opted to add SplitID column to the orders table.
I have added the column as an incremental column with duplicates not allowed.

Also I want to apologize before hand since English is not my main language and this post will probably give nightmares to grammer nazi's

Enlighten me please on what I could have done better.

Again thanks in advance!

 

[jdraw]

Did you look at the link I gave re search?