Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
i am triing to connect an Access database to the rest of my dataset which i load via csv.
Now - i think this is a very basic problem - but at the moment i dont know how to solve it.
So let's say via my csv i load customers. They have a customer ID. I give the name CID.
Now i load the access DB. In this DB there is a table and it also has a customer ID (but this table contains only a fraction of all my customers).
Now i thought if i rename the column in both tables to CID it should create a natural JOIN, right? But at the moment the result is more or less a crossproduct.
So how can i JOIN those 2 tables in a way, that the fraction of customers in my Access table gets connected to the rest of my dataset?
You can use Noconcatenate for those two tables
Have you checked the join in the table viewer (Ctrl + T) to see if they are connected on CID or not?
As Sunny said,
Can you give a screen shot of your data model to see if the tables are associated or not.
Not quite what i want to have. Or at least it did not work.
Maybe it is too hard for me to explain. I try again:
I have 2 tables. Both share the same ID. But one table only has a fraction of the IDs of the other tables.
So now i load the smaller table from my ACCESS DB:
ODBC CONNECT32 TO [MS Access .....];
NoConcatenate
KZ2:
LOAD cust as kz2.cust,
custID as CID,
NNAME as kz2.name;
SQL SELECT *
FROM `KZ02`;
Now i have the CID already defined elsewhere. In this other table i have all my customers. In this new table i load right now- i only have a fraction of curstomers.
Now when i create a table box in QV and load this whole table.
I get a table with CID, kz2.cust, kz2.name.
The problem is , the table does not only show the CIDs from my ACCESS table, but shows all CIDs.
This results in a lot of lines where CID is defined, but the other 2 values are empty.
Now what i want is a table where CID is defined and shown only IF the other 2 values are there as well - or better said - only if the CID was also part of the original ACCESS file. But still the link to my other tables has to work.
For example: If i click in this table on the CID it should choose the correct customer from the big table.
Hope my explanatons are somehow understandable.
Unfortunately i can not post a screenshot - but i am pretty sure the tables are associated.
Like i said in my last answer - now i have a giant table with
CID, kz2.cust, kz2.name
Though in the CID column - i have all CIDs from my other table which has all customers. I would like to only have the customers which were originally in this table. (I am talking about a table box). Maybe i just have to change something in the table box?
Right now i have a lot of these lines
CID, kz2.cust, kz2.name
333, 223,Josh <= ok
334,, <= not ok
335, 123, Carl <= ok
336, 445, Doris <= ok
337,, <= not ok
So in the table box i only want the lines which are entirely filled - like in my original table.
About the association - if i click on the CID it is linked to all my other tables. So association seems to work.
You can try adding a Flag field to your script on the Access table
Access_List:
Load All_your_fields,
'Y' as Access_Flag
from yourAccessDb;
Then include it in your straight table to filter out the data you dont want to see be selecting Y on the Access_Flag field.