How do I cross reference a

[Triger666]

I am trying to use my combo box that is linked to my primary key (workstation number to my inventory) to create a lookup form. But I am unable to cross reference it since my inventory information is found on many tables. So can you help me find a way to use a combo-box to lookup a column that is my primary key (found in all tables) so that I can lookup all the information in my tables from one form.

 

[dynamictiger]

Write a query one step at a time using the combo box data as criteria. If you can't get the query to work your approach won't work either.

 

[MoGryph]

Can you explain a little more about your table architecture?

Do you have tables like MONITOR, WORKSTATION, MOUSE, SPEAKERS, KEYBOARDS, etc? i.e. Different tables for each type of equipment, and then some sort of link-table for attaching equipment to particular locations, or PCs?

I'd consider trying to find a way of putting all equipment in the same table. If you can't find some way of doing this, you might want to consider using a Smart-ID (can't remember if that's what they called it in the old days, but something like that).

I've come up with my own way of handling something like this in Access, but I'm sure I'm not the first-

You can hard-code the smartkey into your query on your tables (Such as "PC" & ID for the PC's table, "MTR" & ID for the Monitors Table, etc). This might make it easier, then you could just parse out the identifier at the beggining of the key.

I1f you need to pull all the tables togethor to get a single large list, use a UNION query. MS Access (at least the 97 version) doesn't have a Wizard to create one, and I don't think the HELP was very usefull at learning it either but in a nutshell, this is what it's like (assuming you have different key names for each table)

SELECT MonitorID AS ID, MonitorSN As SN
       FROM Monitors

UNION

SELECT PcID AS ID, PcSN as SN
       FROM PCs

This will pull both tables togethor, with both (or however many) fields on the resulting recordset, and the resulting field names would be the "As" part.

e.g.

Monitors
-----------
MonitorID    MonitorSN
--           -----
1            01234
2            01235
3            01236

PCs
-----------
PcID    PcSN
--      -----
1       65432
2       65431
3       65430

The UNION'd query result would give you

QueryName
----------------
ID      SN
---     -----
1       01234
1       65432
2       01235
2       65431
3       01236
3       65430

(You CAN sort on SN instead of ID if you wish)

To explain what I meant previously about Smart-Keying it, you can change the original Union query, like so:

SELECT "MR" & [MonitorID] AS ID, MonitorSN As SN
       FROM Monitors

UNION

SELECT "PC" & [PcID] AS ID, PcSN as SN
       FROM PCs

And the output would have Unique keys:

QueryName
----------------
ID        SN
---       -----
MR1       01234
MR2       01235
MR3       01236
PC1       65432
PC2       65431
PC3       65430

You'd just have to remember, that if you're going to store this information, you're going to have to know what table you grabbed it from- so chop of the first 2 characters, and store that, or use it right away, to decide which table to point to. To make things easier here, you could also include a 2 more fields with JUST the id and table info-
i.e.:
(monitor section
MonitorID AS TrueID, "Monitors" As TBLName

(pc section
PCID As TrueID, "PCs" As TBLName

Then you don't have to worry about parsing off the ID, but you will need both pieces of data to point to the right place.

Now, it gets a little ugly looking when you add in the rest of the tables, like MOUSE, KEYBOARDS, SPEAKERS etc, but it's pretty much the same thing. Just add another branch:

UNION
   SELECT "MO" & [MouseID] AS ID, MouseSN AS SN, MouseID AS TrueID, "MOUSES" As TBName

and so on.

I hope I wasn't too long-winded... And I hope this helps you out too. If this isn't what you were looking for, let me know.

-MoGyph
8O)