Table Fields

[MFS]

Okay, Here goes hopefully I can get this out without loosing anyone.

I have two tables In one table I have a Field named Machine in the other table I have a field named Mach_Desc. Now these two tables actually hold the same information but worded differently.

Example:

Table 1 ......................Table 2

Machine 1...... =..... Plant Machine 1

What I want to be able to do with these two tables is say that Machine 1 in Table 1 is the same machine in table 2. So when I make a report it will show both machines as being the same. I will also not be Querying by this Field I will query from a date and it will show me all of my machines in the report.



Now you have to be confused!!!

Please help

[CraigDolphin]

So you want the database to join the two table based on two fields that contain completely different values....

Let me ask you and see if YOU can do what you're asking a dumb computer to do...

Does an Ox equal a Boeing747, a Horsehead Nebula, or a Butter Clam?

...


...



...



no?

Ok then, so this brings us to the question that will make or break this situation.

Do you have a naming convention in your second table that Access can make use of to figure out which value in table 2 matches which value in table 1.

For example, is the value in table 2 ALWAYS the value from Table 1 preceded by the string "Plant " ?

If not, then put a fork in your question because you're done You will have to manually go through table 2 and provide a key column that can be used to directly match up the two tables.

If yes, then you could create a query based on table 2, that uses the Right function to extract only the portion of the field after the "Plant " bit, along with the other fields from that table. Then you can join this query to table one in a second query and do what you want to do.

[MFS]

Example, in table 1 I have data coming in like "Sidney ZSK-133" in table 2 it reads just "ZSK-133".
Hopefully this will help.

Mike

[CraigDolphin]

Not unless the value in table 1 always reads "Sidney " + the value in table 2, it doesn't

[MFS]

The value in table 1 always reads Sidney before the machine description. Table 2 always reads the machine description only.

[CraigDolphin]

Ok then,
make a query of table1 that selects all the fields except the one with the sidney reference. Instead, add a calculated field that has the following formula

MachineDesc: Right([SidneyFieldNameHere],len(SidneyFieldNameHere])-7)

Save this query and call it whatever you want (say qryNewTable1).

Then, make a second query that joins table2 and qryNewTable1 based on the MachineDesc field in qryNewTable1 and the Machine description field in table 2.

Then select whatever fields you want from either table and hopefully you'll get what you want.

[MFS]

Sorry, I tried I just can't seem to get it. I'll have to figure something else out.

[MFS]

Ohh, Thanks for all of your help CraigDolphin.

[CraigDolphin]

If you post a zipped copy of the database in Access 2000 format I could take a look at it.

[MFS]

I would but the one table is linked to a SQL database from our company network. Would it be just as easy to make two tables, name fields in each table as Machine Description. Throw some data in one of the tables like "Sidney ZSK-133 and Sidney ZSK-23" and then in the other table insert data as "ZSK-133 and ZSK-23" Add a date field and then query from that? I would like to have this but I don't want to really trouble you any longer. I'm sure I'll find something out that will work.

Thanks again,

Mike

[CraigDolphin]

Ok then, here's a quickie database that demonstrates how to make this work using what I suggested before. There's no reason that qryNewTable1 could not work on a linked SQL server table just the same as with a native access table.

To see the final result bringing together columns from the two different tables, open qryPullingEverythingTogether

[MFS]

Got it! Just Awesome. I appreciate your help so much. This definatley makes my project a little easier.
Thanks again.
Mike