Group by (Selecting First) after Sorting

[Ken]

Hi,

I am trying to figure out a process from queries to obtain correct data from
a table.

For example, I have 3 fields

Apple Red Big
Apple Green Small

Now I would like to obtain the 2nd row as a result (I do not want to use
individual criteria as I am working on a large transaction table with
history). So my logic is to create one query where I would sort this table in
ascending order on the second field, which gives,

Apple Green Small
Apple Red Big

Then uses a second query (group by) on the first field, the rest using
(First from group by) to get the answer, but in the end I get the first row
instead. What am I doing wrong here?

 

[scubadiver]

What you are doing doesn't sound very logical to me. What information do you
want to get?

 

[Jeff Boyce]

Ken

You posted in the tablesdbdesign newsgroup.

Your post seems to be a 'query'-related question. You'll get more focused
attention in the future if you post in the newsgroup dedicated to the topic.

You "have three fields" ... but you provided the values, not the field
names.

You mention a "transaction table" but don't describe it.

It all starts with the data ... and I don't have a very clear picture yet on
your data structure.

If you want a query that sorts by the first field (containing "Apple"), then
by the second field (containing either "Red" or "Green"), create a query,
pull in those fields, then use the SortBy category to do just that.

Or do I not yet understand your post?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken]

Hi all,

I guess I was not very clear in stating my problem. Currently I have set up
a data warehouse (star schema) for storing customer information history. For
one of the dimension table, I have customer information such as

CustomerID Active Responsibility Period
C707 No None 26
C707 Yes John 27
D800 Yes John 26
D800 Yes David 27
D801 Yes Ken 26
E900 Yes Ken 27

Note: Primary Key is not displayed

Now, for one the analysis I need to compare information from two different
periods (not the information I show above, but in the fact table). In order
to do this, I must summarize the customer dimension for each customerID
before I join back to the fact table.

So the result I wanted is

CustomerID (Key) Active Responsibility Period
C707 Yes John 27
D800 Yes David 27
D801 Yes Ken 26
E900 Yes Ken 27

the result should show the latest information for each CustomerID (in this
case, D801 only exists in period 26). That is the reason why I try to use the
"Sort by Period" then "Group by CustomerID" Approach where all other fields
uses the "First/Last for Total Criteria".

Another reason I ask is if I use a group by query (Q2, using FIRST or LAST
for total) based on another query (Q1), the result is not consistent with the
record order in (Q1), but if I used Q2 on a table directly, then it is fine.

I do not know if this make sense, but if you have any suggestions of how I
can obtain the results, please let me know. Thanks.

 

[KARL DEWEY]

Use two queries unless you know subqueries --
CustMaxPeriod ---
SELECT CustomerID, Max([Period]) AS MaxPeriod
FROM YourTable
GROUP BY CustomerID;

SELECT CustomerID, Active, Responsibility, Period
FROM YourTable INNER JOIN ON CustMaxPeriod YourTable.CustomerID =
CustMaxPeriod.CustomerID AND YourTable.Period = CustMaxPeriod.MaxPeriod;