Grouping Query results 1

[kastaman]

Hi there,

In my access query, I'd like to group the file names according to the first 3 letters of the file:

File name = Query Grouping result
Alpine = Alp*
Alpo = Alp*
Beta = Bet*
Bettin = Bet*



Thanks in advance,

Kastaman

 

[lespaul]

what's the rest of your query?

Leslie

 

[kastaman]

Leslie,

My query looks like:

SELECT DISTINCTROW tbl_Client_Data.Client, [199911].Jobname, Sum([199911].Impressions) AS [Print Impressions]
FROM 199911, tbl_Client_Data
GROUP BY tbl_Client_Data.Client, [199911].Jobname
HAVING (((tbl_Client_Data.Client) Like "*mellon*") AND (([199911].Jobname) Like "ALP*" Or ([199911].Jobname) Like "BET*"));

This work fine but the result shows all jobnames. I'd like the results grouped and summarized according to ALP & BET.


Thanks in advance,

Kastaman

 

[lespaul]

modify it slightly to:

SELECT DISTINCTROW tbl_Client_Data.Client, [199911].Jobname, Sum([199911].Impressions) AS [Print Impressions]
FROM 199911, tbl_Client_Data
WHERE (((tbl_Client_Data.Client) Like "*mellon*") AND (([199911].Jobname) Like "ALP*" Or ([199911].Jobname) Like "BET*"))
GROUP BY tbl_Client_Data.Client, [199911].Jobname;

Leslie

 

[kastaman]

Hi there,

Here is an example of how the query works and what I actually wanted to accomlpish:

Current Query:
Client Jobname Print Impressions
Mellon EITFKGCN 24,303.00
Mellon EITFKGEN 15,183.00
Mellon EITFKNBN 12,906.00

Expected Result:
Client Jobname Print Impressions
Mellon EIT 52,392.00

I'd like the query to group and summarize and changing the jobname value to EIT for example.




Thanks in advance,

Kastaman

 

[CosmoKramer]

Kastaman,

Try changing to something like this, using the Left function to display and group on only the first three characters:

SELECT DISTINCTROW tbl_Client_Data.Client, Left([199911].Jobname,3), Sum([199911].Impressions) AS [Print Impressions]
FROM 199911, tbl_Client_Data
WHERE (((tbl_Client_Data.Client) Like "*mellon*") AND (([199911].Jobname) Like "ALP*" Or ([199911].Jobname) Like "BET*"))
GROUP BY tbl_Client_Data.Client, Left([199911].Jobname,3);

Hoc nomen meum verum non est.

 

[kastaman]

Cosmo!!

That is exactly what I needed. Thanks so much.

Thanks in advance,

Kastaman

 

[lespaul]

Kastaman,

You could have gotten this answer with the first response if you had included your sample data and expected results in your original posting.

Leslie