Access 2003 SQL frustrations

[BJZeak]

given 2 tables containing:
sid's; 10,20,30,40,50,60,70,80
cid's; 11,12,13,14,15,16,17
and one Table S referencing to cid's to sid

sid cidw cids cidt
10    11   11    -
20     -   13    -
30    13    -    -
40    11   11    -
50    14    -    - 
60     -   12   15   
70    16   17    -
80     -   14    -

attempting to generate Table C referencing sid's to cid

 cid sids sidw cidt
  11   10   10    -
  11   40   40    -
  12   60    -   15
  13   20   30    -
  14   80   50    -
  16    -   70    -
  17   70    -    -

currently working with the following test query which gets close ... the reason I started this thread was that the actual query where table S is actually output from another Query where the Select top 1 returns an error (query won't run) stating W.cidw may return more then one instance of s.cids ... this test query at least runs ... the only thing I can think of is the actual query results are affecting the 2nd Query ... also having other issues where I make a change to a query and the only way it will run is if I copy the SQL code to a brand new Query.

Test Query
SELECT 
   IIF(cids <> "",cids, cidw) AS cid, 
   s.sid AS sids, 
   IIf((cids<>"" And cids=cidw) Or (cidw<>"" And Len(cids)=0),
       sid,
      (select top 1 sid from s as w where w.cidw = s.cids)
   ) AS sidw, 
   s.cidt
FROM s
ORDER BY IIF(cids<>"",cids,cidw);

Been working on this query for several days now so hoping someone can point me in the right direction

 

[BJZeak]

Tables feeding Query S are:

Table sid
   sid Text(3)
   stat Text(9) [Active | InActive | Temporary]

Table x
   sid Text(3) 
   sType Text(1) [S | W | T]
   cid Text(5)
   sdt Date(Medium)
   xdt Date(Medium)

Table s Query
Select 
   sid.sid as sid, 
   w.cid as cidw, 
   s.cid as cids, 
   t.cid as cidt, 
   t.sdt as sdt, 
   t.xdt as xdt
From ((sid 
Left Join [
      Select
         x.sid,
         x.sType,
         x.cid
      From x
      Where x.sType = "W"
      ]. As w On sid.sid = w.sid) 
Left Join [
      Select
         x.sid,
         x.sType,
         x.cid
      From x
      Where x.sType = "S"
      ]. As s On sid.sid = w.sid) 
Left Join [
      Select
         x.sid,
         x.sType,
         x.cid,
         x.sdt,
         x.xdt
      From x
      Where x.sType = "T"
       ]. As t On sid.sid = t.sid
Where sid.stat = "Active" AND (s.cid & w.cid) <> "";

 

[dhookom]

Please use TGML to format your posts like I have used.


I'm not sure your data matches with your desired outcome. The closest I can easily get is to first to create a normalizing union query:

SELECT S.SID, S.CIDW AS CIDValue, "CIDW" AS CID
FROM S
WHERE CIDW is not null
UNION ALL
SELECT S.SID, S.CIDS, "CIDS"
FROM S
WHERE CIDS is not null
UNION ALL
SELECT S.SID, S.CIDT, "CIDT"
FROM S
WHERE CIDT is not null;

Then create a crosstab from the the union query:

TRANSFORM Avg(quniNormalizeS.SID) AS AvgOfSID
SELECT quniNormalizeS.CIDValue
FROM quniNormalizeS
GROUP BY quniNormalizeS.CIDValue
PIVOT quniNormalizeS.CID;

Result
[pre]
CIDValue CIDS CIDT CIDW
11 25 25
12 60
13 20 30
14 80 50
15 60
16 70
17 70
[/pre]

Your output is missing the 15-60, has a 12-15, 14-50 and 16-70 are in the wrong columns.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[BJZeak]

Thank-you dhookom

SID refers to Storage ID
sTypes; W = Winter, S = Summer, T = Temporary

Your Union ALL query, with the minor addition of DISTINCT for all SELECT statements, reproduces the 12 rows from table X ... the W S T cid suffix originates from the sType field in table X ... the 8 rows of table S were created using Query S (cid's to sid) from table X

SID CID sType 
10  11  CID W
30  13  CID W
40  11	CID W
50  14  CID W
70  16  CID W
10  11  CID S
20  13  CID S
40  11  CID S
60  12  CID S
70  17  CID S
80  14  CID S
60  15  CID T

Understand that Table C is the actual sid's to cid I am trying to achieve ... whether starting from Table X or S or a combination of them ... there are 4 rules:

1) cid 11 is a special case
   a) it always is assigned to both the S and W of any SID
   b) it can be assigned to more the one SID
   c) it would never be assigned to a T
i.e. rows 1 and 4 of Table s ... cid 11 blocks both the S and W sid from being selected by any other CID

2) all other CID's can, at most, have only one W, and one (S or T) with the valid combinations for any CID, other 
   then 11 being:
   a) S         Table S cid 12   
   b) S & W     Table S cid's 13 and 14
   c) T         Table S cid 15
   d) T & W     Not represented in Table S Data but cid 15 could be assigned to sid 20 W or sid 80 W
   e) W         Table S cid 16 

3) cid's assigned as T override sid S assignments for the SD to XD dates

So regardless of starting from Table S (cid's to sid) or X (Basic assignments), Table C in my first post is the desired output ... reordering table X may illustrate this better

11 10 S     cid(11) sids(10) sidw(10)
11 10 W
11 40 W     cid(11) sids(40) sidw(40)
11 40 S
12 60 S     cid(12) sids(60) sidw(  ) cidt(15)
15 60 T
13 20 S     cid(13) sids(20) sidw(30)
13 30 W
14 80 S     cid(14) sids(80) sidw(50)
14 50 W
16 70 W     cid(16) sids(  ) sidw(70)
17 70 S     cid(17) sids(70) sidw(  )

I started from Table S because it already provide sids and cidt ... my thoughts were that I only needed to use a subquery to fill in the missing sidw value ... that is when things like renaming test queries wouldn't run because access appears to use the query name as part of its internal processing ... this just requires an annoying copy, delete, new, and paste ... but the other extreme frustrations is the Access "The Field cid could refer to more than one table listed in the From Clause of your SQL Statement" even though my test example works (especially since the subquery uses SELECT TOP 1 which should only ever return one row)

 

[dhookom]

TOP 1 is more reliable if it is ordered by a unique value.

Can you please use TGML?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[BJZeak]

TGML is checked by default, but, for whatever reason, TT doesn't appear to honor the user entered formatting unless the text is manually encapsulated in "code" Tags ... perhaps there should be another entry by default that encapsulates the entire thread as [text] [/text] using the same logic as "Code" so the user's entered formatting is maintained without having to manually encapsulate text in "code" tags?

Took your advice and attempted to add Order By both to my existing subquery and or the main query itself ... neither of these by themselves or together solved the problem ... I then removed ALL supporting information detail pertaining to the Query so that I would end up with just the ID's in all queries ... again still no joy ... the only difference between my posted results is that S is a Table in my testing but is a Query in real life, per my second post

(Select Top 1 blah From whatever As something Where something.blah = whatever.blah Order By something.blah)
or
(Select Top 1 blah From whatever As something Where something.blah = whatever.blah Order By whatever.blah)

Both still do not work for me in Access when "whatever" is the result of another query

if:
- Table X is Queried with Query S ... whatever is S ... in this case, Query C fails to run
- Table S is manually populated with the results of Query S, and Query C uses Table S instead of Query S results Query C runs

 

[BJZeak]

Thanks again for your feedback

Still unclear what Access is doing behind the scenes (based on my testing, it appears this failure may have something to do with using a query as the input data source instead of using a table) ... even after rebuilding (dump and reload) the Assignment Data (Table X) and adding the appropriate Top 1 and Order By details, the original Query C is still failing to use Query S as it's input source (query C refuses to run because ACCESS thinks the subquery will return too many records, even though I explicitly ask it to give me the first row) ... HOWEVER ... I have finally found a work around ... rather then using Query S's output for the input to Query C, I have copied and modified Query S ... by just eliminating the Winter CID Details, then adding the WSID SubQuery this new Query is working 100% with one minor tweak to handle the exception for CID 11 ... now understanding why most people just walk away from this stuff ... even with 30+ years of varied Consumer and Enterprise RDBMS experience, what should have been a fairly simple task, turned in to a real hair puller.

Original Query S

Query S
Select 
   sid.sid,
   w.cid as cidw, 
   s.cid as cids, 
   t.cid as cidt, 
   t.sdt as sdt, 
   t.xdt as xdt
From ((sid 
Left Join [
      Select
         x.sid,
         x.sType,
         x.cid
      From x
      Where x.sType = "W"
      ]. As w On sid.sid = w.sid) 
Left Join [
      Select
         x.sid,
         x.sType,
         x.cid
      From x
      Where x.sType = "S"
      ]. As s On sid.sid = w.sid) 
Left Join [
      Select
         x.sid,
         x.sType,
         x.cid,
         x.sdt,
         x.xdt
      From x
      Where x.sType = "T"
       ]. As t On sid.sid = t.sid
Where sid.stat = "Active" AND (s.cid & w.cid) <> "";

Query C (modified Query S)

Query C
Select
   iif(s.cid = "11",sid.sid,(Select Top 1 sid From x As wx Where wx.sType = "W" and wx.cid = s.cid)) as wsid,   
   sid.sid as ssid, 
   s.cid as cids, 
   t.cid as cidt, 
   t.sdt as sdt, 
   t.xdt as xdt
From ((sid 
Left Join [
      Select
        x.sid 
        x.cid
      From x
      Where x.sType = "W"
      ]. As w On sid.sid = w.sid) 
Left Join [
      Select
         x.sid,
         x.sType,
         x.cid
      From x
      Where x.sType = "S"
      ]. As s On sid.sid = w.sid) 
Left Join [
      Select
         x.sid,
         x.sType,
         x.cid,
         x.sdt,
         x.xdt
      From x
      Where x.sType = "T"
       ]. As t On sid.sid = t.sid
Where sid.stat = "Active" AND (s.cid & w.cid) <> "";