1 query 2 tables help?

[mejia.j88]

hello all,



I have a database with a drop down box containing part numbers. upon choosing a part number, it auto populates two other fields, height and width.

I have two tables that include part numbers (we have types of products).

goal: create a query that includes both of the tables' part numbers that i can use with my combo box to still autopopulate height and width.

ex. Part Number 0001 from table 1; after being chosen will autoP. height .5 inches and width .04 inches.

the next part number needed could be from table two. Part Number 2002, etc etc as above.

this is the SQL i have for the query:

SELECT DIODES.P_NUMBER, DIODES.NUM_ACTIVE, DIODES.DIE_SIZE, DIOCHIPS.P_NUMBER, DIOCHIPS.NUM_ACTIVE, DIOCHIPS.DIE_SIZE
FROM DIODES, DIOCHIPS;

i cant put the tables together because it will mean constantly updating them every time a new part number is created.

comments/suggestions?

thanks a ton guys.

[jzwp11]

Normalization rules dictate that all similar items be in the same table, so my recommendation is to put all parts in 1 table. You can add a field that you can use to distinguish the two groups of parts.

Can you provide a little more detail as to what you are doing with your database?

[Robeen]

i cant put the databases together

do you really mean 'two databases'?
Or do you mean two tables?

If you REALLY can't get all this data into one Table - you can write a Union query that combines the data from the two tables.
It would look something like this [not tested - just example] :

 
SELECT P_NUMBER, NUM_ACTIVE, DIE_SIZE
FROM DIODES
UNION ALL
SELECT P_NUMBER, NUM_ACTIVE, DIE_SIZE
FROM DIOCHIPS

What this will give you is something that looks like it is all in one table [which is how it should be, really].

I hope this helps.

[mejia.j88]

Robeen,
That worked perfectly; it gives me the Part Number as column 0, stack size (height) as column 1, and cut size (width) as column 2.

after doing the union query, i run into a problem when i populate a part number with a null entry for stack size.

this is my code for auto populating.

Private Sub txtpart_number_afterupdate()
Me.txtStack_Size = Me.txtPart_Number.Column(1) + 2
Me.txtCut_Size = Me.txtPart_Number.Column(2)
End Sub

do i need to create some if else statement to deal with the null entries?

[Robeen]

Research the Nz function.
It has this syntax:
Nz(Expression,0)
What it does is analyze what you put in 'Expression' and if it is Null - replaces the Null with 0. You can replace the 0 with something else - ti doesn't HAVE to be 0.