combining tables with a twist

[jimrward]

tableA has a unique key (key1), this can occur many times in tableB (key1), tableB (key2) occurs the same number of times in tableC

It is a sort of lookup of codes which finally resolves to a text string, I want to be able to link all this together to get the text value from tableC appended to all the data in tableA, effectively all the keywords at the end of the data, these can be from 0 to N.

It would have been easier if the original designer had just put the ref codes in table A instead of the method used. I have been looking at UNION and CROSSTAB with no success

 

[SydneyGeek]

Hi Jim, if i understand correctly you have Table A linked one-to-many to Table B, and Table B linked one-to-many to Table C (or possibly one-to-one). And you want a field in Table C aligned with data in Table A. OK so far?

In the query builder, add all 3 tables. If the links don't appear between the tables, drag and drop to connect them. Now double-click the asterisk in Table A to get all fields, and the field you want to line up in Table C. View the query and you should have what you want. Use the query for reports, but leave the tables s they are. Don't try to put the Table C field in Table A -- you'll have redundant data entry if you do (which is why the designer set it up that way)

Does that help?

Denis

 

[jimrward]

when viewing the query I get multiple lines in the new table depending on the number of keywords that exist in tableB, tableC.

Ideally I want one line, with the multiples appended to the end of the record

 

[jimrward]

the format I want is as follows

record 1, k1,k2,k3...kn

currently I get

record1, k1
record1, k2
record1, k3
.
.
.
record1, kn

I have tried using DISTINCT but with no success

 

[SydneyGeek]

Hmmm... you'll need some code to do that. How often will this data be changing?

Denis

 

[jimrward]

Denis

Thanks for input, I have decided to run with the multiple row thing and then export as EXCEL and write a < 20 line macro to shift the data to another sheet a new line everytime the pimary key changes.

Thankfully it is a one off, extracting data from Visual Fox pro, library management system into SQL server based something else.

Boy do these quite librarians know how to write the most crap, obtuse code in their own little vertical domain, grrrrrr

Lets close the call on that note