Group By being a pain in the butt

[shad0w4life]

Alrighty ummm

I have Columns that contain


{Document Name} | {VERSION} | {DATE} | {CLIENT DOC #} | {RELEASED BY} | {CHRONICLE_ID}


And lots more




What Is happening is There are many duplicate document names that are different because of their Version (Eg. there's versions 1A 1B 1C 2A 3 4D 5E 5D 6 7 8A 8B 8C)

but the chroicle ID is different for each Number change

So the 1A B C have a chronicle id of 1
2A has a chronicle id of 2
3 chronicle id of 3


So what I have to do is get the highest Version per Document Name which should be simple as I should just group by chronicle id and take the highest value in version

problem is that I need to have all those extra fields such as date, document name, client doc #, released by etc etc

and they are specific per document name

 

[Norie]

Why not do a seperate query to get the highest value for the version by chronicle ID?

You can then create a new query based on that and the original table.

You would join the query and table by version and chronicle ID.

 

[shad0w4life]

how the heck would I do that join query

I have it create a new table where version and chron id match the original and have it to unique records and it almost doubles the # of results I got on the group by table

 

[Norie]

Why are you creating a new table?

To do the join query just add the table and query in QBE then drag the fields as usual.

 

[shad0w4life]

make a table cause I like to look for mistakes etc

err in query builder
I have my original table

then the query that pulls out chron and version max

I make a query to compare chronid and version max

and nothing shows up if i have inner join on the 2 fields

if I have two tables I end up with duplicates galore i have it set to an inner join for chronid and version

 

[Norie]

First Query:

SELECT Table1.[CHRONICLE_ID ], Max(Table1.[VERSION ]) AS MaxOfVERSION
FROM Table1
GROUP BY Table1.[CHRONICLE_ID ];

Second Query:

SELECT Table1.[Document Name ], Table1.[VERSION ], Table1.[DATE ], Table1.[CLIENT DOC # ], Table1.[RELEASED BY ], Table1.[CHRONICLE_ID ]
FROM Query2 LEFT JOIN Table1 ON (Query2.MaxOfVERSION = Table1.[VERSION ]) AND (Query2.[CHRONICLE_ID ] = Table1.[CHRONICLE_ID ]);

Query2 in the second query is the first query, if you know what I mean.

 

[shad0w4life]

This is probably simple and I hope it is

There are duplicate versions in my original and due to me only matching version and chronid i'm ending up with duplicates that are different in the non important fields so is their anyway to just make it show 1

and thank you for all the help I've been getting pretty upset at this and can't think clear.