Unique Query Dilema

[Jason Lopez]

I have a query that I have been asked to create that is very foreign to me
and I would almost think to be impossible. However, I throw this out here
to anyone that might know if it as some of the craziest things have been
shown to be possible. So, without further delay, here's my problem.

I have a query of historical data. The data relates to signatures that are
acquired from a scanner for a project that people are to sign off on. Each
signature is from someone that is in a particular department. So, project 1
could have four signatures associated with it. Project 2 could have 2.
There is no set number per project. However, dates are recorded with each
signature that is scanned. So, a table would look something like this:

Project; Signature; Dept; SigDate
1; John Do; Leg; 1/1/2008
1; Jane Smith; Control; 1/3/2008
2; Jack Frost; Control; 1/3/2008

The Query would sort the dates for each project from oldest to newest. But
then, there would need to be a calculation made with each signature of how
long it was from the previous signature to their own in days. In the case
of the very first person to sign, it would be calculated from when the
project started. So, a resulting query would hopefully look like:

Project; Signature; Dept; SigDate; TimeSign
1; John Do; Leg; 1/1/2008; 3
1; Jane Smith; Control; 1/3/2008; 2
2; Jack Frost; Control; 1/3/2008; 4

However, how would I program the query to look at the previous record to
evaluate if it makes a calculation based on previous signer or when the
project started? And, then, would the output have to be to a new table in
order to produce the graphs and reports from?

I look through this and am not sure if it is possible. Additional
information I have searched for yields nothing beneficial to create this
type of query. Yet, I have not tried all resources as I am hoping that
there might be a solution that someone here knows. So I hope someone can
help me out on this one.

Jason

 

[Lord Kelvan]

ok you havent given us enough information

i am guessing you have a project table

projectid; projectstartdate; .......

and a signature table

Project; Signature; Dept; SigDate

and you have this data in the signature table

1; John Do; Leg; 1/1/2008
1; Jane Smith; Control; 1/3/2008
2; Jack Frost; Control; 1/3/2008

and in the project table you would have something like

1;12/29/2007
2;12/30/2007

and then you want a calculation done
so the first signature for each project would be based on its relevant
project start date

1; John Do; Leg; 1/1/2008;3
2; Jack Frost; Control; 1/3/2008;4

and then dates after it would be based on the dates before it not the
project start date

1; Jane Smith; Control; 1/3/2008;2

am i correct in these assumtions if not please tell me what i am wrong
on and if i am right about the tables please give me an relevant
example of the project table

Regards
Kelvan

 

[Lord Kelvan]

ok i am so a genious ^_^

for the above thing to work you need two queries

query1

SELECT signature.project, project.projectstartdate,
signature.Signature, signature.Dept, signature.SigDate, (SELECT
Count(project) + 1 FROM signature AS sig WHERE sig.sigdate <
signature.sigdate and sig.project = signature.project) AS recordnum
FROM project INNER JOIN signature ON project.project =
signature.project
ORDER BY signature.project, signature.SigDate;

and save this as signatures

now this query select the information from the signature table adn the
project table to get the project start date and add a control source
so you can compare the values to get the count of days between

then the magic query

SELECT signatures.project, signatures.Signature, signatures.Dept,
signatures.SigDate, IIf([sigdate] In (select top 1 sig.sigdate
from signatures as sig
where sig.project = signatures.project),[sigdate]-[projectstartdate],
(select top 1 signatures.sigdate-sig.sigdate
from signatures as sig
where sig.project = signatures.project and sig.recordnum =
signatures.recordnum-1)) AS TimeSign
FROM signatures;

save this as whatever

takes the control source in the query signatures and compares them to
get you the result

my raw test data

signature table
sigid project Signature Dept SigDate
1 1 John Do Leg 1/01/2008
2 1 Jane Smith control 3/01/2008
3 2 Jack Frost control 3/01/2008
4 1 frank leg 4/01/2008

Project table
project projectstartdate
1 29/12/2007
2 30/12/2007

and the result

project Signature Dept SigDate TimeSign
1 John Do Leg 1/01/2008 3
1 Jane Smith control 3/01/2008 2
1 frank leg 4/01/2008 1
2 Jack Frost control 3/01/2008 4

as i said if the above structure isnt yours please tell me and ill
make the modifications to compensate for it but all i need is the
control source for the project start date to be confirmed and i can
finilise the queries for you

hope this helps

Regards
Kelvan

 

[KARL DEWEY]

NOT FULLY TESTED -----Use two queries. The first sets an order value. The
second subtracts the dates and adds one.
ProjectSEQ –
SELECT Q.[Project], Q.[Signature], Q.[Dept], Q.[TimeSign], (SELECT COUNT(*)
FROM YourTable Q1
WHERE Q1.[Project] = Q.[Project]
AND Q1.[Signature] = Q.[Signature]
AND Q1.[Dept] = Q.[Dept]
AND Q1.[SigDate] < Q.[SigDate])+1 AS Order
FROM YourTable AS Q
ORDER BY Q.[Project], Q.[Signature], Q.[Dept], Q.[SigDate];

SELECT Project, Signature, Dept, [ProjectSEQ].SigDate,[ProjectSEQ
_1].SigDate, [ProjectSEQ _1].[SigDate]-[ProjectSEQ].[SigDate]+1 AS TimeSign
FROM [ProjectSEQ] LEFT JOIN [ProjectSEQ] AS [ProjectSEQ _1] ON
[ProjectSEQ].Group = [ProjectSEQ _1].Group
WHERE ((([ProjectSEQ _1]Order)=[ProjectSEQ].Order]+1));

 

[Jason Lopez]

I will plug this in and see if it works. If it pulls the data that my boss
is looking for, then this group is awesome to work with.

Jason

 

[Jason Lopez]

I have plugged it into my database and changed the table names in order to
match the actual tables. I only hit a snag when I am at the second query
where I put in the following:

(select top 1 signatures.sigdate-sig.sigdate
from signatures as sig
where sig.project = signatures.project and sig.recordnum =
signatures.recordnum-1)) AS TimeSign

Something in here I have put in wrong. Or I am not understanding what this
part is supposed to do. Can this portion be explained. Especially the part
where is says "sig.project = signatures.project and sig.recordnum =
signature.recordnum-1."

Jason

I will plug this in and see if it works. If it pulls the data that my boss
is looking for, then this group is awesome to work with.

Jason

ok i am so a genious ^_^

for the above thing to work you need two queries

query1

SELECT signature.project, project.projectstartdate,
signature.Signature, signature.Dept, signature.SigDate, (SELECT
Count(project) + 1 FROM signature AS sig WHERE sig.sigdate <
signature.sigdate and sig.project = signature.project) AS recordnum
FROM project INNER JOIN signature ON project.project =
signature.project
ORDER BY signature.project, signature.SigDate;

and save this as signatures

now this query select the information from the signature table adn the
project table to get the project start date and add a control source
so you can compare the values to get the count of days between

then the magic query

SELECT signatures.project, signatures.Signature, signatures.Dept,
signatures.SigDate, IIf([sigdate] In (select top 1 sig.sigdate
from signatures as sig
where sig.project = signatures.project),[sigdate]-[projectstartdate],
(select top 1 signatures.sigdate-sig.sigdate
from signatures as sig
where sig.project = signatures.project and sig.recordnum =
signatures.recordnum-1)) AS TimeSign
FROM signatures;

save this as whatever

takes the control source in the query signatures and compares them to
get you the result

my raw test data

signature table
sigid project Signature Dept SigDate
1 1 John Do Leg 1/01/2008
2 1 Jane Smith control 3/01/2008
3 2 Jack Frost control 3/01/2008
4 1 frank leg 4/01/2008

Project table
project projectstartdate
1 29/12/2007
2 30/12/2007

and the result

project Signature Dept SigDate TimeSign
1 John Do Leg 1/01/2008 3
1 Jane Smith control 3/01/2008 2
1 frank leg 4/01/2008 1
2 Jack Frost control 3/01/2008 4

as i said if the above structure isnt yours please tell me and ill
make the modifications to compensate for it but all i need is the
control source for the project start date to be confirmed and i can
finilise the queries for you

hope this helps

Regards
Kelvan

 

[Lord Kelvan]

ok DO NOT REPLACE THE WORD SIG AT ALL

sig is a custom thing dosnt amtter what databse you use that why it
says select valeu from signature AS sig the as gives the table
signature a new name temporarly in that query so it can be compared to
the actual table signature

only replace signature with the actual table name and any field names

hope this helps

Regards
Kelvan

 

[Lord Kelvan]

sorry for double posting

it is the same in the first query ill place *** where you can change
something


SELECT ***signature.***project, ***project.***projectstartdate,
***signature.***Signature, ***signature.***Dept,
***signature.***SigDate, (SELECT
Count(***project) + 1 FROM ***signature AS sig WHERE sig.***sigdate <
***signature.***sigdate and sig.***project = ***signature.***project)
AS recordnum
FROM ***project INNER JOIN ***signature ON ***project.***project =
***signature.***project
ORDER BY ***signature.***project, ***signature.***SigDate;

and save this as signatures


then the magic query

SELECT ***signatures.***project, ***signatures.***Signature,
***signatures.***Dept,
***signatures.***SigDate, IIf([***sigdate] In (select top 1
sig.***sigdate
from ***signatures as sig
where sig.***project = ***signatures.***project),[***sigdate]-
[***projectstartdate],
(select top 1 ***signatures.***sigdate-sig.***sigdate
from ***signatures as sig
where sig.***project = ***signatures.***project and sig.recordnum =
***signatures.recordnum-1)) AS TimeSign
FROM ***signatures;

the select in the middle of each of the queries is a sub query not a
seperate one there are only 2!!!!! queries

hope this clears it up

Regards
Kelvan

 

[Lord Kelvan]

sorry for double posting and retracting the last message

it is the same in the first query ill place *** where you can change
something


SELECT ***signature.***project, ***project.***projectstartdate,
***signature.***Signature, ***signature.***Dept,
***signature.***SigDate, (SELECT
Count(***project) + 1 FROM ***signature AS sig WHERE sig.***sigdate <
***signature.***sigdate and sig.***project = ***signature.***project)
AS recordnum
FROM ***project INNER JOIN ***signature ON ***project.***project =
***signature.***project
ORDER BY ***signature.***project, ***signature.***SigDate;


and save this as signatures


then the magic query


SELECT signatures.***project, signatures.***Signature,
signatures.***Dept,
signatures.***SigDate, IIf([***sigdate] In (select top 1
sig.***sigdate
from signatures as sig
where sig.***project = signatures.***project),[***sigdate]-
[***projectstartdate],
(select top 1 signatures.***sigdate-sig.***sigdate
from signatures as sig
where sig.***project = signatures.***project and sig.recordnum =
signatures.recordnum-1)) AS TimeSign
FROM signatures;


the select in the middle of each of the queries is a sub query not a
seperate one there are only 2!!!!! queries

remember signature is YOUR TABLE signature is the name of query1 and
sig is something you dont change because it is a dependant in a sub
query.

hope this clears it up


Regards
Kelvan

 

[Lord Kelvan]

cough that is "signatures is the name of query 1" not signature

 

[Jason Lopez]

YEAH!! Your explanation helped me see where I screwed up. It works now.
Perfectly! Thank you so much. My boss will be very happy.

 

[Lord Kelvan]

no problem hopefully you learned something as well for future
reference

Regards
Kelvan

 

[Jason Lopez]

It'll take me a while to understand more completely the mini-query that was
thrown in there and how it plays into the select top phrasing that was
there... But, all with due time. Just means more library time.

Jason

 

[Lord Kelvan]

basically the mini query is needed to compare the two values because a
database qurey can only see one row at a time so the main query sees
the row you want to assign the time sign value to and the mini or sub
query reads the row before it to see the difference allowing the query
to essentionally see two rows at the same time.

hope this helps

Regards
Kelvan