Add two tables together

[brew]

Hi,



I have two tables for two different different types of cases, one for bespoke products one for 'off the shelf' items.

Both tables have a field called 'Product No', the the Product Number's in the bespoke table are all unique but the Product Numbers in the other table can repeat.

Our Finance guy has asked if there is a way to combine the two 'Product No' fields in either a query or another table to make his job a little easier.

Table 1

Product No
A
B
C
D

---------------
Table 2

Product No
E
F
G
H


Into----------

newTable/Query

Product No
A
B
C
D
E
F
G
H

I'm sure its relatively simple, but I seem to be having some serious brain freeze issues.

Thanks

[jzwp11]

Normalization rules dictate that all similar items be in the same table, so all products should be in the same table, you can add a field to distinguish "off the shelf" versus bespoke. You might call this field producttype

I would add this field to your bespoke table and populate it accordingly. Then also add an autonumber primary key field to the table. If the current product field is designated as no duplicates, change that now. Then run an append query to add the records from the "off the shelf" product table. As you do the append you can also populate the producttype field (using an expression)

Out of curiosity, why would you have a table with non-unique product numbers?

[brew]

Thanks jzwp11,

The tables are customer order tables, for the off the shelf parts there is a table with Product No, description etc.

The off the shelf table I'm talking about links to the table with customer details, so on reports i can have data from both field displayed in the same report.

Product No ----- Customer ------- Descripton------order date -----etc

[jzwp11]

I'm not following. In a typical order type database you would have at least 4 tables as follows:

tblCustomers
-pkCustID primary key, autonumber
-txtCustomerName
-txtAddr
etc.

tblProducts (all products)
-pkProductID primary key, autonumber
-txtProductNumber
-txtProductDesc
etc.

tblCustomerOrders
-pkCustOrderID primary key, autonumber
-fkCustID foreign key to tblCustomers
-dteOrder (order date field)

tblCustomerOrderDetails
-pkCustOrdDetailsID primary key, autonumber
-fkCustOrderID foreign key to tblCustomerOrders
-fkProductID foreign key to tblProducts
-longQtyOrdered (long number integer field, may be different in your case)


prefix definitions:
txt=text datatype field
pk=primary key field, autonumber datattype
fk=foreign key field, must be a long number integer field to match its corresponding autonumber field in the related table
dte=date/time field
long= long number integer field but not a foreign key field


You would create a query using the tables above and then base a report on that query.

[brew]

I see your point, I think I will re jig my db around your example.

Thanks

[jzwp11]

You're welcome; please post back with any questions on the design.