SQL Question

[clamum]

I have a table that looks like this:
Original table

And I'm trying to write an SQL statement that produces a result set that looks like this:
After query

Any suggestions?

 

[snapper316]

What program are you using?

If it is MS Access you could try a DLookup

 

[clamum]

Originally posted by: snapper316
What program are you using?

If it is MS Access you could try a DLookup

Yes, I'm using Access. I'm not sure what DLookup is but I'll check it out. I should mention that this query needs to be done in a SQL query from within a .NET application too.

 

[WannaFly]

dont know if access supports sub select's,

select (select devaultvalue from <table> where WCP1AttributeDescription='Pump Up-time') as PumpUpTime, (select devaultvalue from <table> where WCP1AttributeDescription='Settled Time') as SettledTime


edit: this might not be the best way, and there can only be one row with those values, or else you should use distinct in the sub's.

 

[snapper316]

http://www.mvps.org/access/general/gen0018.htm


Normal usage

For numerical values:
DLookup("FieldName" , "TableName" , "Criteria = n")

For strings: (note the apostrophe before and after the value)
DLookup("FieldName" , "TableName" , "Criteria= 'string'")

For dates:
DLookup("FieldName" , "TableName" , "Criteria= #date#")



So in your query you'd have 2 columns

pumpTime: DLookUp("DefaultValue","tblOriginalName","WCP1AttributeDescription='Pump Up-Time'")
(As expression if you are grouping)

settleTime: DLookUp("DefaultValue","tblOriginalName","WCP1AttributeDescription='SettledTime
")

notice thats single quotes around the string then the ending quotation

 

[clamum]

I managed to get what I wanted using this query:

SELECT
tbl1.WCP1SONumber AS Job_AID,
tbl1.DefaultValue AS PumpUpTime,
tbl2.DefaultValue AS SettledTime
FROM
tblWCP1SOAttribute AS tbl1, tblWCP1SOAttribute AS tbl2
WHERE
tbl1.WCP1SONumber = @JobID AND
tbl1.WCP1SONumber = tbl2.WCP1SONumber AND
tbl1.WCP1AttributeDescription = 'Pump Up-time' AND
tbl2.WCP1AttributeDescription = 'Settled Time'

I pass in the JobID in the code.

Now this works fine for this query, but thinking about it, there's some queries where instead of just two values I need to select 10 or more. So in that case there would be 10 or more tables in the FROM clause and this query would be pretty big... I don't think that's very efficient. I'll check out your method WannaFly. And snapper316, I haven't tried the DLookUp function yet but I will see if that works.

 

[clamum]

Well I decided to use your subquery method WannaFly. It works good but the queries will be pretty big (though not as bad as my method). Bah, I wish these tables were setup differently. Thanks for the help guys.

 

[brianmanahan]

Whatever you're doing here, it doesn't look like it fits under BNF or 3NF. Or any NF for that matter, LOL.

 

[clamum]

Originally posted by: brianmanahan
Whatever you're doing here, it doesn't look like it fits under BNF or 3NF. Or any NF for that matter, LOL.

Yeah I know. I doubt there's any possibility of changing the table design at this point, unfortunately (unless we move from Access to MS SQL which we may do down the road).

What I'm trying to do basically is query data from our tables and make it match the design of someone else's tables. Problem being is their tables are setup wayyyy better and ours are kinda weird.