Help with query design (1 Viewer)

kirkm · Feb 25, 2017

I'm a bit stuck with a problem that I find even hard to explain.

I want to output all records where ID is (say) 39 from main table "Dat". That seems fairly straightforward

Select Dat.Z, Dat.FieldA, Dat.FieldB, Dat.FieldC from Dat Where Dat.ID= 39

But the output needs to include those with a possible substitute ID so I made a lookup table 'Dupes' with 4 fields and example contents:

ID 39
Z "A4"
SubstituteID 1143
SubstituteZ "B6"

Wanted are records where ID is 1143 (instead of 39) and Dat.Z is 'B6' but shown as 'A4'

I can't seem to get the SQL right, or be sure it can all be done in one statement.
Thanks for any help.

CJ_London · Feb 25, 2017

try something like

Select Dat.Z, Dat.FieldA, Dat.FieldB, Dat.FieldC from Dat LEFT JOIN DUPES ON Dat.ID=Dupes.SubstituteID Where nz(Dupes.ID,Dat.ID)= 39

jdraw · Feb 25, 2017

Perhaps the following

Code:

Select Dat.Z
, Dat.FieldA
, Dat.FieldB
, Dat.FieldC 
, Dupes.z
, DupesSubstituteID
from Dat INNER JOIN Dupes
ON Dat.ID = Dupes.ID
 Where Dat.ID= 39

but you lost me with and Dat.Z is 'B6' but shown as 'A4'

kirkm · Feb 25, 2017

Thanks very much for the replies. I've been trying them out and getting closer (I'm sure the problem is my description).
> but you lost me with and Dat.Z is 'B6' but shown as 'A4'
I want it to be A4 but it's B6 in the table. Both are in Dupes.

Neither A4 or B6 are known apart from existing in Dupes and the ID is 39.

When I run CJ code I get 5 entries with ID 39 and 55 with id 1143. I only want one (the one where SubstituteZ is "B6")

Jdraw result is the 5 entries ID39 and none from ID1143.

and I'm aware this isn't making correct sense. Apologies..

plog · Feb 25, 2017

Demonstrate your issue with data. Provide 2 sets of data:

A. Starting sample data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you expect your query to return when you feed it the data in A.

kirkm · Feb 25, 2017

Thanks, that sounds like a good idea. It took me a while to prepare a good example.I hope the attached includes teh A and B data you wanted.

Table 'Dupe' is populated by a VBA module (that I'm still tweaking) and table Dat is a subset of my data.

The third table shows the desired output. There's 11 entries and 9 are straight copies from Dat but 2 use "Dupe" to get the offset data.

In this case A4 and B1 in ID39 are B6 and Q9 from ID 1143.

Thank you for being interested/helping.

CJ_London · Feb 25, 2017

don't know how you got multiple returns - when I took my suggestion and applied to your data it brings back what you require plus 2 more

Code:

SELECT Dat.AlbumTrack, Dat.TTitle, Dat.TPerformer, Dat.TDuration, Dat.AlbumID, Dupe.SubstituteID, Nz([Dupe].[AlbumID],[Dat].[albumID]) AS Expr1
FROM Dat LEFT JOIN Dupe ON Dat.AlbumID = Dupe.SubstituteID
WHERE (((Nz([Dupe].[AlbumID],[Dat].[albumID]))=39));

However, now having some data I can see what you are trying to get to. Copy and paste this code into a new query

Code:

SELECT Nz([Dupe].[AlbumTrackNum],[dat].[albumtrack]) AS Track, Dat.TTitle, Dat.TPerformer, Dat.TDuration, Dat.Year
FROM Dat LEFT JOIN Dupe ON (Dat.AlbumTrack = Dupe.SubstituteTrackNum) AND (Dat.AlbumID = Dupe.SubstituteID)
WHERE (((Nz([Dupe].[AlbumID],[Dat].[albumID]))=39));

it works based on the data provided

kirkm · Feb 25, 2017

That's pretty amazing CJ, it works perfectly and get me past a major hitch. Now to apply more data to it etc. Thanks very much

(I could never have figured out a query like that !)

kirkm · Feb 26, 2017

CJ I didn't know whether to start a new thread or not... but I added an Order By clause
and it worked. But then I had an order of A1, A10, A2.. etc and I realise why. So in query design grid I tried this (a direct paste of the text)

ORDER BY 10*Asc(Nz([Dupe].[AlbumTrackNum],[Dat].[albumtrack]))+Val(Mid(Nz([Dupe].[AlbumTrackNum],[Dat].[albumtrack]),2));

It works ok from the query grid designer, but as an sql string it gives syntax error(missing Operator) in query expression '10*'.

Do you get the same results, and perhaps know why ?

CJ_London · Feb 26, 2017

providing you do not group the query (can't see why you would) the following should work

ORDER BY Left(Track,1) & format(mid(track,2),"00")

if it doesn't replace Track with

Nz([Dupe].[AlbumTrackNum],[dat].[albumtrack])

kirkm · Feb 26, 2017

Great solution CJ. many thanks. Track wanted a Parameter value so had to use Nz(Dupe... method.

kirkm · Mar 4, 2017

CJ, I've found a snag as I develop this further. Once I add data to tblDupe that offset track shows in the target album, which is what is wanted.

But if I display that tracks own album it's missing. I can't quite see (yet) what is stopping this, but you might ?

I've attached the db again with data to show this condition.
As it is running Query2 shows no track A1. Unless I remove the entry starting with 977 from Dupe. But then Query3 shows no track A1

CJ_London · Mar 4, 2017

follow the logic of the query:

you want to see all 978's but with this one you have substituted 978 with 977, so it is not returned. - and there isn't a 977/A1 anyway.

Change the AlbumID to 978 and substituteID to 977 in the dupe table - but that will only work for 977 if you add an A1 record

kirkm · Mar 4, 2017

I did try to follow the query logic, but it sort of got away on me. You're right - there is no 977/A1 so I want to use 978/A1. It's just a coincidence they're both A1.
I can't follow the query well enough to modify it, but I think it's the 'right' way to do it.
Although if I do as you suggest, won't it be right for 977 but then wrong for 978?

My other method would be to do it in 2 stages, first with a maketable query for those that do exist, then add any substitutes. Although your query seems more correct..

CJ_London · Mar 4, 2017

now I'm not following your logic

The query works as intended - look up an alternative value for a given album/track

your problem is you have a missing record. I'm not clear on what you are trying to achieve, but if there is no record, the query can't look up an alternative.

Perhaps show with the existing data what you expect to see returned for both 977 and 978

kirkm · Mar 4, 2017

If there's no record there'll be no entry in Dat, and no 'offset' in Dupe. What seems to be happening is the Offset is working as intended in Query 2 but Query 3 fails until any entry in Dupes is removed (or factored out).

My logic was grab everything in Dat where ID is xxx, plus anything where ID xxx exists in Dupes. Buts In the latter case use the data pointed to by the substitute ID and track.

I thought a query would do the job, but my knowledge isn't sufficient to get it working. So I'll try a plan B which I've roughly flowcharted and looks feasible.. if not the most efficient. (But there's no great need for speed).

Thanks very much for the discussion and your code though. It showed me a lot and came close, and your Order By method was really good !

Help with query design (1 Viewer)

kirkm

Registered User.

CJ_London

Super Moderator

jdraw

Super Moderator

kirkm

Registered User.

plog

Banishment Pending

kirkm

Registered User.

Attachments

CJ_London

Super Moderator

kirkm

Registered User.

kirkm

Registered User.

CJ_London

Super Moderator

kirkm

Registered User.

kirkm

Registered User.

Attachments

CJ_London

Super Moderator

kirkm

Registered User.

CJ_London

Super Moderator

kirkm

Registered User.

Similar threads

Users who are viewing this thread