sql sub query to access prior values with critera in multipe tables - advice pls

[chaeljc]

hi all



please see attached word doc.

im not very familiar with SQL and am having difficulty writing the code which includes a nested / sub query to access data from a previous record.

i did get it working ok when all the data is in one table however im stumped when the criteria and sub queries relate to different queries (i think lol)

i have been tearing whats left out of my hair for a couple of days now therefore any help would be most appreciated.

kind regards

michael

[pbaldy]

See if this helps:

http://allenbrowne.com/subquery-01.html#AnotherRecord

[chaeljc]

hi paul

many thanks for your help, however your link is where i got my inital sub query knowledge. unfortunately (for me) he is dealing with criteria which is all held in the one table. all web examples i have found are like that. my problem is that the field i want to appear with the records previous data in in one table and the criteria for filtering is in another related query.

it's probably a very simple matter to those expert in SQL, unfortunately im not and having to struggle with it as work databases expand etc.

many thanks

kind regards

michael

[Cap Heresy]

I think this will do what you want. To make it clearer to see, I put a placemarker called "SUB1" at two different places in the query. Below the query you will see "SUB1 =". Take the code below that and use it to replace both "SUB1" placemarkers in the query.

Caveats:
I assumed that RecordStagesID is unique across ALL ProRecID's. If not, some tweaks will be needed.


SELECT
REC1.pkRecordStagesId, REC1.fkProjStageId, REC1.fkProRecId, DAT1.stageStartDate, DAT1.stageFinishDate,

(SELECT IIF (NOT EXISTS SUB1, NULL,
(SELECT DAT2.StageFinDate FROM tblStagesStartFinishDates DAT2
WHERE DAT2.RecordStagesId = SUB1))) AS PriorValue

FROM
tblRecordStages REC1 LEFT JOIN tblStagesStartFinishDates DAT1
ON REC1.pkRecordStagesId = DAT1.fkRecordStagesId;


SUB1 =
(
SELECT MAX(REC2.pkRecordStagesId)
FROM tblRecordStages REC2
WHERE REC2.fkProRecId = REC1.fkProRecId
AND REC2.pkRecordStagesId < REC1.pkRecordStagesId
)

[chaeljc]

Hey cap heresy

many thanks for your speedy reply! much appreciated.

Im actually sitting on a train heading to belfast northern Ireland for a bit if r&r. Lol

Im looking forward ti having a good tinker with your proposal over the bank holiday weekend. Ill be sure to let you know.

Enjoy your weekend, and many thanks again.

Regards

Michael

[pbaldy]

If Cap's method doesn't work, it might be helpful if you posted a sample db and the desired result.

[chaeljc]

Hey pbaldy

yes your idea is a good one ... Ill rustle up a demo and post it.

Id be very interested in you guys thoughts..

manybthanks for your interest.

Regards Michael

[chaeljc]

hi guys n gals

as suggested by pBaldy i have attached a zip containing a demo of the database i am trying todevelop.

open the switchboard and the button from top to bottom will take you through the structure of the db in more or less hirarchical order.

each form has notes written on it to explain the form's function

the key button is 'project record stages & officers assignment'

the sub form has a filtering and and allocation options. i am quite confident with vba to gather user input to compile sql queries embedded in a vb module, which will then be used as the alternate 'filtered' forms' recordsouces.

my SQL is very limited tho, and a you will see that when a manager is allocating stages to workers from the form, it is necessary to refer to the status of the 'stage finished date' for the stage prior to the stage being filtered for. this is so when a manager filters for records which for example stage 2 has not been started, so as to give them to a worker, the filtered recordset only includes records in which work in the previous stage has been completed - denoted by a date in the stage finished field.

i look forward to any help you guys can give as i have been stuck for quite some time on this (its taken sevral days even to realise the existance of SQL sub queries and that they might be the way forward... or not. lol

i hope you find it interesting and feel free to offer what ever comments or insights you feel.

its taken me all morning to compile demo dB so im just off now to have lunch and then try to understand / utilise cap heresy's sub query example.

thanks again.

kind regards

michael (from a SNOWY (!!!) bangor northern ireland.


EDIT: ps you will notice that in the form 'project record stages & officers assignment' in the subform i have used the project stages table to force the 'blank' records for each unassigned stage to be visible - i have found this to be disirable from the managers point of view.

byeee