Grouping years for a report/query etc....

[Aurillius]

Hi there,
This was a thread that I started last year but no one replied to it. Is there someone that can solve this?
....

I just received a huge kickout table from our data company. There is over 10 thousand records. In order to compare this to our records, I have to group the parts by everything except the year, they need to be collected together and then displayed as a range on the report.
Here is an example of the data, the left side is the field name:
YEAR = 1999
MAKE = GMC
MODEL = SONOMA
APP = AXLE REPAIR
POS. = FRONT
LOC. = OUTER
PART# = 1234
What I need is a report/query that will group this data as such:
99-92 GMC SONOMA AXLE REPAIR FRONT....etc.....

Thanks,
Aurillius

 

[SkipVought]

Hi,

What is "99-92"? Does that mean YEAR between 1992 and 1999?

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Aurillius]

Yes, forgot to mention that.

 

[SkipVought]

Then I'd use MS Query via Data/Get External Data/New Database Query -- Excel Files -- YOUR WORKBOOK -- YOUR SHEET containing this data...

The SQL in the Query will look sonething like ...

SELECT Distinct iif(A.YEAR>2000,'00-05',iif(A.YEAR>1991,'92-99','80-91')), A.`MAKE `, A.`MODEL `, A.`APP `, A.`POS# `, A.`LOC# `, A.`PART# `
FROM `D:\My Documents\vba\more query`.`Sheet2$` A

where the source data is in sheet2

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Aurillius]

This code may not be specific enough. You are saying if the year is over 2000 then change the field to "00-05" but what if some year ranges are from 00-03...or 99-02....they could be anything.
...
2000 Toyota Tercel
2001 Toyota Tercel
2003 Toyota Tercel
1998 Hyundai Accent
1999 Hyundai Accent
2000 Hyundai Accent
2001 Hyundai Accent
...to

03-00 Toyota Tercel
01-99 Hyundai Accent

I'm using Access, SQL and/or vba/Excel

 

[SkipVought]

I just defined ranges as an example.

YOU can define ranges any way that you like.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}