Sub Query?

  • Thread starter Thread starter Juls
  • Start date Start date
J

Juls

I have 2 tables

Call time

Ref Date Centre QNo ACW ACD
3816911 38169 1 1 1.1 1.2
3816912 38169 1 2 2.1 2.2
3816913 38169 1 3 3.1 3.2
3816911 38169 2 1 1.4 7
3820011 38200 1 1 1 10
3820012 38200 1 2 2 12
3820013 38200 1 3 3 13

Queues

Ref Centre QNo QName ShNme
11 1 1 Motor NB MNB
12 1 2 Packages NB PNB
13 1 3 SBC NB CNB

What I want is-

Queues.Qno Queues.ShNme Calltime.acw Calltime.acd
1 MNB 1.1 1.2
2 PNB 2.1 2.2
3 CNB 3.1 3.2
When Calltime.date <=38169 and centre = 1 or

Queues.Qno Queues.ShNme Calltime.acw Calltime.acd
1 MNB 1 10
2 PNB 2 12
3 CNB 3 13
When Calltime.date <=38200 and centre = 1 (the Calltime.date is the
date code from excell)
I need the sql to query the database from Excel but just can't get it
to work,
I think I need to do a sub query but I can't work it out.
Many Thanks in advance.
Juls
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't really need a subquery. You can do this by JOINing the 2
tables. Also, your criteria should be Calltime.date = ##### instead of
<=, at least that is what your example dataset indicates.

Suggestion: The CallTime table should use another word for the name of
the [Date] column, since Date is a reserved word in VBA/JET SQL.

Here is a stab at the query (w/ parameters to make it more flexible):

PARAMETERS CallTimeDate Long, CallTimeCentre Integer;
SELECT Q.Qno, Q.ShNme, C.ACW, C.ACD
FROM CallTime As C INNER JOIN Queues As Q
ON Q.Centre = C.Centre AND Q.Qno = C.Qno
WHERE C.[Date] = CallTimeDate AND C.Centre = CallTimeCentre

Perhaps another one using the Ref column in both tables (more
accurate?):

PARAMETERS CallTimeDate Long, CallTimeCentre Integer;
SELECT Q.Qno, Q.ShNme, C.ACW, C.ACD
FROM CallTime As C INNER JOIN Queues As Q
ON Q.Centre = C.Centre AND Q.Qno = C.Qno
AND Right(Q.Ref,2) = C.Ref
WHERE C.[Date] = CallTimeDate AND C.Centre = CallTimeCentre

This one will be a little slower, 'cuz it has to scan the whole table in
order to parse the Right(Q.Ref,2) expression. To avoid this the Q.Ref
column value should be changed from the Date & Ref No (3816911) to just
the Ref No (11), since the Date is already present in the [Date] column.

If the Call Time Ref column was changed as suggested the query could be
re-written like this:

PARAMETERS CallTimeDate Long, CallTimeCentre Integer;
SELECT Q.Qno, Q.ShNme, C.ACW, C.ACD
FROM CallTime As C INNER JOIN Queues As Q
ON Q.Centre = C.Centre AND Q.Qno = C.Qno AND Q.Ref = C.Ref
WHERE C.[Date] = CallTimeDate AND C.Centre = CallTimeCentre

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRES3YechKqOuFEgEQLQKACgrcx5ZJKrXwYgNf14VX+p7QUY1Z4AoJWu
y7zxfPYurh+NHQHzxa4LUAHj
=wsoc
-----END PGP SIGNATURE-----
 
Hi,
Sorry I didn't explain it properly, each date may not have an entry
but I will need to get the the next oldest date and only show one
record for for each Qno.
I've changed the example to show this, sorry,

Call time

Ref Date Centre QNo ACW ACD
13816911 38169 1 1 1.1 1.2
13816912 38169 1 2 2.1 2.2
13816913 38169 1 3 3.1 3.2
23816911 38169 2 1 1.4 7
13820011 38200 1 1 1 10
13820013 38200 1 3 3 13

Queues

Ref Centre QNo QName ShNme
11 1 1 Motor NB MNB
12 1 2 Packages NB PNB
13 1 3 SBC NB CNB

What I want is-

Queues.Qno Queues.ShNme Calltime.acw Calltime.acd
1 MNB 1.1 1.2
2 PNB 2.1 2.2
3 CNB 3.1 3.2
When Calltime.date <=38169 and centre = 1 or

Queues.Qno Queues.ShNme Calltime.acw Calltime.acd
1 MNB 1 10
2 PNB 2.1 2.2
3 CNB 3 13
When Calltime.date <=38200 and centre = 1 (the Calltime.date is the
date code from excell)

I also use the ref as a unique number (I've had to change that to
include centre as each centre will have will have the same Qno) whish
is indexed as I use it to seach for records in excel.

Sorry I didn't explain it properly.
Thanks for the help.
Juls


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't really need a subquery. You can do this by JOINing the 2
tables. Also, your criteria should be Calltime.date = ##### instead of
<=, at least that is what your example dataset indicates.

Suggestion: The CallTime table should use another word for the name of
the [Date] column, since Date is a reserved word in VBA/JET SQL.

Here is a stab at the query (w/ parameters to make it more flexible):

PARAMETERS CallTimeDate Long, CallTimeCentre Integer;
SELECT Q.Qno, Q.ShNme, C.ACW, C.ACD
FROM CallTime As C INNER JOIN Queues As Q
ON Q.Centre = C.Centre AND Q.Qno = C.Qno
WHERE C.[Date] = CallTimeDate AND C.Centre = CallTimeCentre

Perhaps another one using the Ref column in both tables (more
accurate?):

PARAMETERS CallTimeDate Long, CallTimeCentre Integer;
SELECT Q.Qno, Q.ShNme, C.ACW, C.ACD
FROM CallTime As C INNER JOIN Queues As Q
ON Q.Centre = C.Centre AND Q.Qno = C.Qno
AND Right(Q.Ref,2) = C.Ref
WHERE C.[Date] = CallTimeDate AND C.Centre = CallTimeCentre

This one will be a little slower, 'cuz it has to scan the whole table in
order to parse the Right(Q.Ref,2) expression. To avoid this the Q.Ref
column value should be changed from the Date & Ref No (3816911) to just
the Ref No (11), since the Date is already present in the [Date] column.

If the Call Time Ref column was changed as suggested the query could be
re-written like this:

PARAMETERS CallTimeDate Long, CallTimeCentre Integer;
SELECT Q.Qno, Q.ShNme, C.ACW, C.ACD
FROM CallTime As C INNER JOIN Queues As Q
ON Q.Centre = C.Centre AND Q.Qno = C.Qno AND Q.Ref = C.Ref
WHERE C.[Date] = CallTimeDate AND C.Centre = CallTimeCentre

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRES3YechKqOuFEgEQLQKACgrcx5ZJKrXwYgNf14VX+p7QUY1Z4AoJWu
y7zxfPYurh+NHQHzxa4LUAHj
=wsoc
-----END PGP SIGNATURE-----

I have 2 tables

Call time

Ref Date Centre QNo ACW ACD
3816911 38169 1 1 1.1 1.2
3816912 38169 1 2 2.1 2.2
3816913 38169 1 3 3.1 3.2
3816911 38169 2 1 1.4 7
3820011 38200 1 1 1 10
3820012 38200 1 2 2 12
3820013 38200 1 3 3 13

Queues

Ref Centre QNo QName ShNme
11 1 1 Motor NB MNB
12 1 2 Packages NB PNB
13 1 3 SBC NB CNB

What I want is-

Queues.Qno Queues.ShNme Calltime.acw Calltime.acd
1 MNB 1.1 1.2
2 PNB 2.1 2.2
3 CNB 3.1 3.2
When Calltime.date <=38169 and centre = 1 or

Queues.Qno Queues.ShNme Calltime.acw Calltime.acd
1 MNB 1 10
2 PNB 2 12
3 CNB 3 13
When Calltime.date <=38200 and centre = 1 (the Calltime.date is the
date code from excell)
I need the sql to query the database from Excel but just can't get it
to work,
I think I need to do a sub query but I can't work it out.
Many Thanks in advance.
Juls
Click to expand...
Click to expand...
 
Back
Top