Need help on a JET SQL Query for multiple series graphics :( 1

[Idmpc]

Hey everyone

I know the title might have been confusing, but here's what I've been trying to do (and haven't been successful at all):

I have a MS Access 2003 database, and am using ADO Components to access its contents with my Delphi program (So it uses JET SQL).

In my database, I have the following fields (among others):
DateTime, APN

here's a example of some of the data:
DateTime APN
08/10/2008 05:06:07 - APN1
08/10/2008 07:08:09 - APN1
08/10/2008 07:07:07 - APN2
08/11/2008 10:10:10 - APN2
08/11/2008 10:10:10 - APN1
... ...

So what happens is that I have APN1 and APN2 repeating for different times. What I need is to be able to make a query that will return me how many times all APNs are repeating themselves on each day inside a specified interval.

For example, the query I need would result the following:

Date APN1 APN2
08/10/2008 2 2
08/11/2008 1 1

I know how to do it if I had to list only 2 columns, one for the date and the second one for a SINGLE APN, as I'd be using the aggregate function COUNT, and then group by date. But what I need is a different column for every APN found on the database, so I can easily export that result to Excel and generate graphics that have Date on the X Axis and every APN as a series on the Y axis.

Can anyone help me, please?

Thanks in advance!

 

[jaxtell]

I don't use jet SQL, so I'm not sure if this is valid.

select trunc(t1.date), t1.apn as apn1, t2.apn as apn2 from (select trunc(date) as date, count(apn) as apn from mytable group by trunc(date)) t1, (select trunc(date) as date, count(apn) as apn from mytable group by trunc(date)) t2 where t1.date = t2.date

I'm pretty sure that'll work in Oracle...

 

[dhookom]

I'm not sure how you got a total count of 6 with only 5 sample records. You could try a SQL view like:

SELECT DateValue([DateTimeField]) AS DateColumn, Abs(Sum([APN]="APN1")) AS APN1, Abs(Sum([APN]="APN2")) AS APN2
FROM tblWithNoName
GROUP BY DateValue([DateTimeField]);

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

I think what he needs is a crosstab query....I'm not sure of the exact syntax, but you can follow the wizard.

Take this query:

SELECT DateValue([DateTimeField]) AS DateColumn, APN, Count(*) FROM TableName

that should give you each date and APN combination and the count:
[tt]
Date APN Count
08/10/2008 APN1 2
08/10/2008 APN2 1
08/11/2008 APN2 1
08/11/2008 APN1 1
[/tt]
[aside]above you show APN2 on 8/10 with 2, that's a typo?[/aside]

now you can transform this to a crosstab query that will give you the results you are looking for...and it will be dynamic for however many APN# there are..

Leslie

Have you met Hardy Heron?

 

[dhookom]

I am a little concerned about using a crosstab from an application outside of Access. I might work okay. If it works, there doesn't really need to be a preliminary query.

Duane
Hook'D on Access
MS Access MVP

 

[Idmpc]

Hi everyone! Thanks for all the help!
I'll try to be less confusing

I really DID make a typo on my first sample, here's the sample again:

08/10/2008 05:06:07   -      APN1
08/10/2008 07:08:09   -      APN1
08/10/2008 07:07:07   -      APN2
08/11/2008 10:10:10   -      APN2
08/11/2008 10:10:10   -      APN1

And here's how my result should be:

Date         APN1     APN2
08/10/2008    2        1
08/11/2008    1        1

JAXTELL, when I tried to use your query, I got the following error message:

The SELECT statement includes a reserved word on an argument name that is misspelled or missing, or the punctuation is incorrect.

And I couldn't do much about it, cause I didn't understand much of the code, sorry (I did change the "mytable" to my table's name)

DHOOKOM: Your query works and is very close to what I need, however I do not know how many different APNs there are, no t even their names, so the columns should be generated automatically, showing all of the different APNs that are stored on the DataBase

LESPAUL: Your code returned the following error:

You tried to execute a query that does not include the specified expression 'DateValue ([DateTimeField])' as part of an aggregate function.

And if I try to add "GROUP BY DateValue ([DataTimeField])" to the query, it will return the same message, but now it's complaining about the APN field, not the DateValue one.

So, does anyone know how to list all APNs on the columns, just like DHOOKOM's code, but with the columns actually being generated automatically, since I do not know the name of all the APNs (and there are more than 60 different APNs, and more to be added)?

Thank you very much for the support!

 

[dhookom]

Create a crosstab from your table that

Field: DateValue([DateTimeField])
Totals: Group By
Crosstab: Row Heading

Field:APN
Totals: Group By
Crosstab: Column Heading

Field:APN
Totals: Count
Crosstab: Value


Duane
Hook'D on Access
MS Access MVP

 

[Idmpc]

dhookom

Thanks for the help, and I'm very sorry about my lack of knowledge, but what is / how can I create a crosstab from my table?

Thank you!

 

[Idmpc]

dhookom, thanks a lot for your help!
I went into access help file and read about those crosstab queries
So I created one just as you specified and it's working perfectly!

Here's the code I used for it to work on my ADO Components:

TRANSFORM Count(Table.APN) AS CountOfAPN
SELECT FORMAT (Table.DateTime, 'dd/mm/yyyy')
FROM Table
GROUP BY FORMAT (Table.DateTime, 'dd/mm/yyyy')
PIVOT Dados.[APN];

Once again, thank you very much for the help!

 

[lespaul]

and for future reference, when using a GROUP BY clause you have to include all the non-aggregate fields. So in the query above it would be:

GROUP BY DateValue([DateTimeField]), APN

my bad for neglecting to include it.

Duane, I'm pretty sure Delphi will just treat it as a result set, that it won't matter that it's a cross tab. If it does, the OP can post in Forum102 and we'll help him there! I would probably use the COUNT query I did above and do the other processing in Delphi instead of in the query.

Les