Help Still Needed?

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

Juls

Hi,
This a repost of a question I didn't explain properly.
I've changed the example to show this, sorry, I've looked through previous
postings but still can't work it out.

Call time

Ref Dte 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
23816921 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'll need an each of the calltime.qno, dte is a the excel date code,
abd each QNo will not have an entry for each date but newest before a
set date will be required.

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

Try the following SQL Syntax:

SELECT Queues.Qno Queues.ShNme Calltime.acw Calltime.acd
FROM [Call time] INNER JOIN Queues ON [Call time].QNo = Queues.QNo
WHERE [Call time].Centre = 1 AND [Call time].QNo = 1;

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights






--------------------
| From: (e-mail address removed) (Juls)
| Newsgroups: microsoft.public.access.queries
| Subject: Help Still Needed?
| Date: 11 Aug 2004 02:09:23 -0700
| Organization: http://groups.google.com
| Lines: 44
| Message-ID: <[email protected]>
| NNTP-Posting-Host: 129.35.81.17
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1092215364 14156 127.0.0.1 (11 Aug 2004
09:09:24 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Wed, 11 Aug 2004 09:09:24 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!news-out!newsfee
d.cwix.com!easynet-monga!easynet.net!news.glorb.com!postnews2.google.com!not
-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:209704
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi,
| This a repost of a question I didn't explain properly.
| I've changed the example to show this, sorry, I've looked through previous
| postings but still can't work it out.
|
| Call time
|
| Ref Dte 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
| 23816921 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'll need an each of the calltime.qno, dte is a the excel date code,
| abd each QNo will not have an entry for each date but newest before a
| set date will be required.
|
| Sorry I didn't explain it properly.
| Thanks for the help.
| Juls
|
 
Hi Eric,

I can't get the code to work, it asks for a Input or query called
'Call Time'?
I don't really know much about Access at all, but I have managed to
get what I want using 3 queries with the following SQL syntax

Query1
SELECT CallTime.QNo, CallTime.Dte
FROM CallTime
GROUP BY CallTime.QNo, CallTime.Dte, CallTime.Centre
HAVING (((CallTime.Dte)<=[Month]) AND ((CallTime.Centre)=1));

Query2
SELECT Query1.QNo, Max(Query1.Dte) AS MaxOfDte
FROM Query1
GROUP BY Query1.QNo;

Query3
SELECT Queues.QNo, Queues.ShNme, CallTime.ACW, CallTime.ACD
FROM (Queues INNER JOIN Query2 ON Queues.QNo = Query2.QNo) INNER JOIN
CallTime ON (Query2.MaxOfDte = CallTime.Dte) AND (Queues.QNo =
CallTime.QNo)
WHERE (((CallTime.Centre)=1))
ORDER BY Queues.QNo;

Any idea how to combine them into 1 sql statement? if this is possible
or if I'm on the wrong tracks alltogether?

Many thanks for your help.
Juls

Hi Juls,

Try the following SQL Syntax:

SELECT Queues.Qno Queues.ShNme Calltime.acw Calltime.acd
FROM [Call time] INNER JOIN Queues ON [Call time].QNo = Queues.QNo
WHERE [Call time].Centre = 1 AND [Call time].QNo = 1;

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights






--------------------
| From: (e-mail address removed) (Juls)
| Newsgroups: microsoft.public.access.queries
| Subject: Help Still Needed?
| Date: 11 Aug 2004 02:09:23 -0700
| Organization: http://groups.google.com
| Lines: 44
| Message-ID: <[email protected]>
| NNTP-Posting-Host: 129.35.81.17
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1092215364 14156 127.0.0.1 (11 Aug 2004
09:09:24 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Wed, 11 Aug 2004 09:09:24 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!news-out!newsfee
d.cwix.com!easynet-monga!easynet.net!news.glorb.com!postnews2.google.com!not
-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:209704
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi,
| This a repost of a question I didn't explain properly.
| I've changed the example to show this, sorry, I've looked through previous
| postings but still can't work it out.
|
| Call time
|
| Ref Dte 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
| 23816921 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'll need an each of the calltime.qno, dte is a the excel date code,
| abd each QNo will not have an entry for each date but newest before a
| set date will be required.
|
| Sorry I didn't explain it properly.
| Thanks for the help.
| Juls
|
Click to expand...
 
Hi,

You initially said your table name was Call Time, note the space.
Using such a name implies the need to bracket it, ... FROM ... [Call Time]
....
Sure, if your table name was indeed, CallTime, no space, the brackets were
not required.



Hoping it may help,
Vanderghast, Access MVP

Juls said:
Hi Eric,

I can't get the code to work, it asks for a Input or query called
'Call Time'?
Click to expand...

(...)
 
Hi,
Yep, I'd included a space when I shouldn't have. I've tried the
statment but it dosn't give the results I'm after. I need to know the
latest details for each Qno. I've managed to get the results using the
3 queries, can I combine the queries into 1 sql statment.
Many Thanks
Juls

Michel Walsh said:
Hi,

You initially said your table name was Call Time, note the space.
Using such a name implies the need to bracket it, ... FROM ... [Call Time]
...
Sure, if your table name was indeed, CallTime, no space, the brackets were
not required.



Hoping it may help,
Vanderghast, Access MVP

Juls said:
Hi Eric,

I can't get the code to work, it asks for a Input or query called
'Call Time'?
Click to expand...

(...)
Click to expand...
 
Hi,
Yep, I'd included a space when I shouldn't have. I've tried the
statment but it dosn't give the results I'm after. I need to know the
latest details for each Qno. I've managed to get the results using the
3 queries, can I combine the queries into 1 sql statment.
Many Thanks
Juls
Michel Walsh said:
Hi,

You initially said your table name was Call Time, note the space.
Using such a name implies the need to bracket it, ... FROM ... [Call Time]
...
Sure, if your table name was indeed, CallTime, no space, the brackets were
not required.



Hoping it may help,
Vanderghast, Access MVP

Juls said:
Hi Eric,

I can't get the code to work, it asks for a Input or query called
'Call Time'?
Click to expand...

(...)
Click to expand...
Click to expand...
 
Hi,
I've now managed to get it down to 2 queries as below but I'm really
struggling to combine them into 1 sql statment.

Query1
SELECT CallTime.QNo, Max(CallTime.Dte) AS Maxofdte
FROM CallTime
WHERE (((CallTime.Dte)<=[Month]) AND ((CallTime.Centre)=1))
GROUP BY CallTime.QNo;

Query2
SELECT Queues.QNo, Queues.ShNme, CallTime.ACW, CallTime.ACD
FROM (Queues INNER JOIN Query1 ON Queues.QNo = Query1.QNo) INNER JOIN
CallTime ON (Query1.QNo = CallTime.QNo) AND (Query1.Maxofdte =
CallTime.Dte)
WHERE (((CallTime.Centre)=1))
ORDER BY Queues.QNo;

Any help would appreciatted.
Juls


Hi,
Yep, I'd included a space when I shouldn't have. I've tried the
statment but it dosn't give the results I'm after. I need to know the
latest details for each Qno. I've managed to get the results using the
3 queries, can I combine the queries into 1 sql statment.
Many Thanks
Juls
Michel Walsh said:
Hi,

You initially said your table name was Call Time, note the space.
Using such a name implies the need to bracket it, ... FROM ... [Call Time]
...
Sure, if your table name was indeed, CallTime, no space, the brackets were
not required.



Hoping it may help,
Vanderghast, Access MVP

Hi Eric,

I can't get the code to work, it asks for a Input or query called
'Call Time'?

(...)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top