SQL Query Assistance

[susieqtotallynew]

I am writing a report in Access. The report is based on a MSSQL database.

The query I am working on involves three tables:
1. Base
2. Characteristic Link
3. Characteristic

The tables are connected as follows:
1. Base ID is linked to ID on Characteristic Link Table
2. Characteristic ID on Characteristic Link Table is linked to Characteristic ID on Charactertistic table

For each ID from the Base table, there can be many characteristic ids on the characteristic table.

In the query, I wish to return a UNIQUE list of Base IDs that do NOT have a matching entry of characteristic id xxx, xyz, or pdq in the attribute table.

I have pulled a list, but it contains multiple entries for each id (quite sure this is for every listing where the limiting charactertistic is not true). I am pretty sure my issue has to do with the join type, but I am not sure how to correct it.

Can you help?

 

[PWise]

select baseid
from base
left join(Select [Characteristic ID] 
          from [Characteristic Link]
          where [Characteristic ID] in (x,y,z))link
on link.[Characteristic ID] =base.[Characteristic ID] 
where link.[Characteristic ID] is null

x,y,z= the [Characteristic ID] for xxx xyz pdq

 

[susieqtotallynew]

I am working on another SQL query.
I have the following tables with the listed relevant fields:
1. Base Table
a. ID
2. Characteristic Link
a. ID
b. Characteristic_ID
c. date_value
d. numeric_value
e. string_value
f. amount_value
g. yesno_value

ID from the Base Table is Linked to ID from the Characteristic Link Table.
One Base Table ID can have many characteristic_IDs.

What I would like to do is create a query that lists each base table ID and the date, numeric, string, amount, or yesno value that corresponds with each of its characteristic ids on one line. So, for example, if ID #1 had characteristics red, 9/21/2009, yes, they would be listed as:
ID Char Char1 Char2
1 red 9/21/2009 yes
Is this possible?

Thank you in advance.

 

[dhookom]

I believe a crosstab query should work.

Is only one of the "Value" fields not null in any given Characteristic Link record?

Duane
Hook'D on Access
MS Access MVP

 

[susieqtotallynew]

Duane,
Thanks for your input, but the problem with that is that the query should only return the specified characteristic ids. Say that characteristic ids include 1, 2, 3, 4, 5. The query might ask only to return 2 and 3. And, yes, in any given case, 2 and 3 could be null for a particular ID. Any other suggestions?
Thanks again...

 

[dhookom]

You can use the Column Headings property of a crosstab query to specify which values to include in the generated columns.

Is only one of the "Value" fields not null in any given Characteristic Link record?

Assuming these are the fields in the Characteristic Link table:
[tt][blue]ID
Characteristic_ID
date_value
numeric_value
string_value
amount_value
yesno_value[/blue][/tt]
Would I expect to see no more than one "value" field populated?


Duane
Hook'D on Access
MS Access MVP