Access Query returning more rows than in queried data base 3

[awesomeBA]

I created a query in Access using the wizard that returns more rows than in the queried DB. It is a inner join using the address field from two tables. Fairly new DB user, (Obviously). Please help!

 

[jebry]

Hi!

It sounds like a cartesean join type of problem. In this case, if you have multiples of certain addresses in both tables, you could get this result. As an example, if the same address shows up in both tables three times, your query will return nine records (3x3). The best solution would be to find another field you can join on along with the address field that would give you a one-to-one match.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[awesomeBA]

You are absolutely right. I do have multiples of practically all the addresses in both tables. I will look for an additional field. Thanks!

 

[awesomeBA]

I checked and there isn't another field that will work when creating a 1:1. As an alternative, I'd like to insert a column from table 2 into table 1 using the address as the relationship. Can this work? What would be the most simple approach?

 

[jebry]

Hi!

What fields do you have in each table?



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[pbrodsky]

If you are trying to extract information that depends only on the address field and you have duplicates of the address in both tables, you should reconsider the way your database is normalized.

 

[awesomeBA]

I created these two tables from data dumps from database that are known not to be normalized.

The allmachines tables includes data pertaining to a machine, ie comp_ id, host_name,serial_Num,user, cost_center, address1, city, country ......

The second table contains real estate information, building_code, Building_Location,city,country......

i need to assign teh building code from table 2 to all rows in table 1

 

[pbrodsky]

UPDATE a1 INNER JOIN A2 ON a1.addr1 = A2.addr2 SET A2.code2 = [a1].[code1];

This worked as expected.

 

[awesomeBA]

a1 and A2 are these table names or field names?

 

[PHV]

table names

 

[pbrodsky]

Table names - I just created a couple of small tables to test the query.

 

[awesomeBA]

1)I created a column for the new field(GRDLBldcode) in table 2 (all workstation)

2)I plugged my tablename/field information into the statement.

UPDATE Allworkstation INNER JOIN Netcatch ON netcatch.building_name= Netcatch.building_name SET Allworkstation.GRDLBldcode = [Netcatch].[Building_code];

3)I received " Join expression Not supported error message"

 

[PHV]

ON Allworkstation.building_name= Netcatch.building_name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[awesomeBA]

Thanks PHV.

Access is asking me to enter the Parameter value for Allstation.Building name.

this concerns me. I need teh values to pull from table 1 (netcatch)

 

[awesomeBA]

Its actually Allstation.building_name

 

[awesomeBA]

Allstation.building was not the name of the field it is address1.I corrected it and it is running.I will keep you posted.

 

[PHV]

Can you please post the schemas of Netcatch, Allstation and Allworkstation with relationships too ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[awesomeBA]

The relationship between the two is that address1 and building_name are the same.

Allstation contains all necessary info for analysis except the building_code from Netcatch, which I mapped as GRDLBldcode in Allworkstation.

Allworkstation ( there are 20 field names in this table)
comp_ id
host_name
serial_Num
username
cost_center
address1
city
country
(etc)
Netcatch
building_code
Building_Location
building_name
city
country


The query is still running. Is this normal?

 

[awesomeBA]

The query ran sucessfully. Thanks to All!

It updated changes to 178k rows. My table only contains 122k.
Can I assume the other 58k are due to the cartesean join issue and that because they are not present my table row count, that they do not impact my result table?