Query return max value

[LeoM]

Good morning everyone, I have the following case:

This is my Original Table:

TASK_LIST_GRP	TASK_LIST_COUNTER	W1	W2	W3	W8	M1	M2	M3	M4	Y1
OMBIRD	01									X
OMBIRD	01
OMBIRD	02		X
OMBIRSQ0	01									X
OMBPCRQ1	01					X		X
OMBPCRQ1	01					X
OMBPCRQ1	01							X
OMBPCRQ1	01
OMBPCRQ1	01
OMBPCRQ1	02					X
OMBTAC	01

Considering that the Max value in the above table is given by the order of the 9 frequencies (W1 < W2 < W3, etc.) i would like to build a query who will return the following:

TASK_LIST_GRP	TASK_LIST_COUNTER	MAX FRQ
OMBIRD	01	Y1
OMBIRSQ0	01	Y1
OMBPCRQ1	01	M3
OMBPCRQ1	02	M1
OMBTAC	01	NO PKG

So the query should go trough the same key (TASK_LIST_GRP & TASK_LIST_COUNTER) and check which is the max frequency (based on the order). If not any frequency is available, should mention "NO PKG".

Hope it is clear, appreciate you usual and kindly support.
Cheers,
L.

 

[ebs17]

Your table does not correspond to the expected table design for a database.

Microsoft Access tips: Don't use Yes/No fields to store preferences

Explains why not to use a heap of Yes/No fields in a table to store preferences, and how to use a relational design instead. Example database for Microsoft Access

www.allenbrowne.com

The first correct step should be to break down this table into a many-to-many relationship.

W < M < Y

This cannot be used in a useful way. In the table for frequencies there should be a number for sorting and thus for a simple maximum determination. With a little skill, this number can already be represented by the primary key.

 

[LeoM]

Thanks for the answer.
Sorry but i don't understand your first point: it seems to me I don't use any "Yes/No" field, also is too late in this moment to change the structure, anyway thanks for suggestion.
Saying this, let's do in different way, i will manage the order later. How to get the following (i already tried with IIF function but i have exceed the limit, actually i have almost 20 frequencies, the table i showed is only one part) : so what the query should do for each records, just return the first frequency it is find. If not frequencies are finded, then NO PKG.

TASK_LIST_GRP	TASK_LIST_COUNTER	MAX FRQ
OMBIRD	01	Y1
OMBIRD	01	NO PKG
OMBIRD	02	W2
OMBIRSQ0	01	Y1
OMBPCRQ1	01	M3
OMBPCRQ1	01	M1
OMBPCRQ1	01	M3
OMBPCRQ1	01	NO PKG
OMBPCRQ1	01	NO PKG
OMBPCRQ1	02	M1
OMBTAC	01	NO PKG

Cheers,
L.

 

[ebs17]

also is too late in this moment to change the structure

Then you can forget about a reasonable query. SQL works row-oriented, not column-oriented.
But with VBA and the appropriate skills you can solve everything, even if it is more complicated and time-consuming.

With such an Excel-typical structure, you can also switch to Excel for processing. But replicating Excel thinking and logic in Access usually doesn't make sense.

 

[Gasman]

Think you would need a recordset for that plus a table to hold the values.
What happened to ombird and 2 in your output?

 

[LeoM]

Think you would need a recordset for that plus a table to hold the values.
What happened to ombird and 2 in your output?

In that case, since I have only one frequency, the max will be itself.

 

[Gasman]

In that case, since I have only one frequency, the max will be itself.

You do not know that though, until you have inspected all the fields?

 

[Pat Hartman]

also is too late in this moment to change the structure, anyway thanks for suggestion.

It is never too late to fix a design flaw like this. All you will be doing going forward is working around the way your data is stored. Therefore, the longer the issue goes unfixed, the less likely you are to ever fix it so the flaw becomes permanent until you get tired of working with the bad schema and convince yourself that Access is just bad software and too hard to use, when in fact, the issue is that your design is incorrect.

Also, think about all the things that would have to change if you needed to add a new frequency.

What you have is a spreadsheet. You don't have a relational table.

The frequency data needs to be stored in a separate table. One row per entry. This table is "sparse". That means that rows exist only when a particular frequency is specified. The first and third OMBPRCQ1, 01 rows will have two rows each in the new table. All the other original rows will have only one.

This change will cause you to change your data entry to use a subform rather than a matrix.

To solve the existing problem with a bandaid rather than a permanent solution, use a union query. That will cause you to generate 20 rows per one row - one for each of the present and also missing frequency entries. Then you can group the union and get the max value. The frequence codes need to be numeric because Max won't work on the text values you have since W is less then M.

 

[LeoM]

It is never too late to fix a design flaw like this. All you will be doing going forward is working around the way your data is stored. Therefore, the longer the issue goes unfixed, the less likely you are to ever fix it so the flaw becomes permanent until you get tired of working with the bad schema and convince yourself that Access is just bad software and too hard to use, when in fact, the issue is that your design is incorrect.

I partially agree on this, at certain point of the project and especially if you get the application from someone else, we need to understand the time required and if it is really convenient to do a change which may involve many others things and so goes in a loop of modification which hardly may impact the application and its use, or to keep as it is since long time and to find a work around which at least solve the issue. Saying this, I fully agree that as design is wrong.

Also, think about all the things that would have to change if you needed to add a new frequency.

What you have is a spreadsheet. You don't have a relational table.

Well, it is not like that. This is an 100% relational table coming from the CMMS (SAP in this case) as it is. So it is not my application which is wrong. There are no possibility to add further frequencies.

The frequency data needs to be stored in a separate table. One row per entry. This table is "sparse". That means that rows exist only when a particular frequency is specified. The first and third OMBPRCQ1, 01 rows will have two rows each in the new table. All the other original rows will have only one.

No true that raw exist only when a particular frequency is specified. Frequency columns are always present if a Group and Counter are present. Group and Counter may have 1 , multiple or no frequency associated but as raws are always present. Then i agree that the correct and easy way is to have one raw for each frequency.

This change will cause you to change your data entry to use a subform rather than a matrix.

To solve the existing problem with a bandaid rather than a permanent solution, use a union query. That will cause you to generate 20 rows per one row - one for each of the present and also missing frequency entries. Then you can group the union and get the max value. The frequence codes need to be numeric because Max won't work on the text values you have since W is less then M.

As "work around" solution I did as follow: I used exactly an union query to have the frequency used by each Group and Counter in a raw. Something like that:

TASK_LIST_GRP	TASK_LIST_COUNTER	MAX FREQ
OMCOBLGG	41	0
OMCOBLGG	41	M1
OMCOBLGG	42	0
OMCOBLGG	42	M3
OMCOBLGG	42	M6
OMCOBLGG	42	Y1
OMCOBLGG	42	Y2
OMCOBLGG	42	Y3
OMCOBLGG	43	0
OMCOBLGG	43	M3
OMCOBLGG	43	M6
OMCOBLGG	43	Y1
OMCOBLGG	43	Y2
OMCOBLGG	43	Y3

Then I will use another query joining the union query with an intermediate table which give me the order of each frequency (join the field MAX FREQ of union with FREQ of the intermediate table). Something like that:

TASK_LIST_GRP	TASK_LIST_COUNTER	MAX FREQ	TL_FREQ_SEQ
OMCOBLGG	41	M1	5​
OMCOBLGG	42	Y2	13​
OMCOBLGG	42	Y3	14​
OMCOBLGG	42	Y1	11​
OMCOBLGG	42	M6	9​
OMCOBLGG	42	M3	7​
OMCOBLGG	43	M6	9​
OMCOBLGG	43	Y1	11​
OMCOBLGG	43	Y2	13​
OMCOBLGG	43	Y3	14​
OMCOBLGG	43	M3	7​

Now to get the max of each group and counter i need to get the max of TL_FREQ_SEQ field using 2 more queries, one to get the max and one to get the frequency. At the end i will have the following:

TASK_LIST_GRP	TASK_LIST_COUNTER	MAX FREQ
OMCOBLGG	41	M1
OMCOBLGG	42	Y3
OMCOBLGG	43	Y3

This is the result i need, i'm confident is not the best solution but solve the problem. Then i fully agree there is an initial problem of structuring and that a more easy solution may be possible.
Thanks to all for kindly support, is always good lesson learn anyway.
Cheers,
L.

 

[Gasman]

Well, it is not like that. This is an 100% relational table coming from the CMMS (SAP in this case) as it is. So it is not my application which is wrong. There are no possibility to add further frequencies.

That is not relational.?
Looks like it could be a crosstab from that system to me?

 

[ebs17]

This is an 100% relational table coming from the CMMS (SAP in this case) as it is.

Is the table an export/import or a direct link to SAP?
Furthermore, if you have a view, a query or a report in front of you, it also looks like a table.
As well as: Not only developer gods work in SAP. Great software and software names do not guarantee outstanding performance.
This table is a matrix table and is far from normalization and reasonable relational table.

I am often amazed at how intensively and persistently people search for reasons for not applying obviously correct suggestions.
I'll put it this way: breaking down the table above into a proper many-to-many relationship takes just a few minutes. From such a structure, the “table” can be created on the fly using a crosstab query, which can then be used to (transitionally) serve dependent, structural processing.
But you would have the opportunity to fundamentally improve your structures.

Regarding the first paragraph: An import is an interface and ALWAYS offers the possibility of fundamental restructuring in favor of its own functionality. A UNION query was briefly mentioned above as an auxiliary step for normalization. However, with database-sized amounts of data, you will quickly run into performance problems.

Even if the SAP table were a link to SAP, I would quickly be able to create the sensible structure mentioned using temporary tables. The initial effort to do this quickly pays off, even with this one task according to the topic title. Once you have recognized something as right, you can believe in it and start implementing it.

 

[LeoM]

Is the table an export/import or a direct link to SAP?

Is a view from SAP , or better, is a view to a database which is mapped to SAP. I also sure they build this field, because it was a requirement and there is also another problem, the type of the each frequency field is "Long Text" hence not very usable (even in a possible crosstab query).

Furthermore, if you have a view, a query or a report in front of you, it also looks like a table.
As well as: Not only developer gods work in SAP. Great software and software names do not guarantee outstanding performance.

Fully agree!

This table is a matrix table and is far from normalization and reasonable relational table.

I am often amazed at how intensively and persistently people search for reasons for not applying obviously correct suggestions.
I'll put it this way: breaking down the table above into a proper many-to-many relationship takes just a few minutes. From such a structure, the “table” can be created on the fly using a crosstab query, which can then be used to (transitionally) serve dependent, structural processing.
But you would have the opportunity to fundamentally improve your structures.

I'm not a person who want to find reasons to not applying the suggestion, otherwise i was not here to ask support. At the same time i'm realistic and try to solve the problem if the suggestion, which is easy and straight forward for you, is not the same for me. I was the first to say that it was not the best solution, but it gives the result i need but this not means i don't want to apply the obviously correct solution. Saying this, can you give me an example of a proper many to may relationship table considering my actual tables ? So i will see how i can do accordingly.

Regarding the first paragraph: An import is an interface and ALWAYS offers the possibility of fundamental restructuring in favor of its own functionality. A UNION query was briefly mentioned above as an auxiliary step for normalization. However, with database-sized amounts of data, you will quickly run into performance problems.

Even if the SAP table were a link to SAP, I would quickly be able to create the sensible structure mentioned using temporary tables. The initial effort to do this quickly pays off, even with this one task according to the topic title. Once you have recognized something as right, you can believe in it and start implementing it.

Cheers,
L.

 

[ebs17]

Saying this, can you give me an example of a proper many to may relationship table considering my actual tables ?

A real example should be based on a real database. So upload an example database with some representative records here, including the existing indexes and relationships.

 

[Pat Hartman]

This is an 100% relational table coming from the CMMS (SAP in this case) as it is.

No, it is not properly normalized. You are experiencing that by the problems you are running into trying to use the data. I have interacted with SAP off and on at various clients for more than 40 years. Mostly I was building COBOL (originally) or Access (lately) applications to solve the problems that the all purpose SAP claimed to solve but didn't. I can assure you that the tables are NOT normalized. As @ebs17 already mentioned, this thing you think is a table, might actually be a view and that view might have denormalized a perfectly normal structure because that was the spec for the data request. You might be able to investigate this and find that there are actually two normalized tables that join to build the view and that you can link to (or more likely import) and use more easily.

The temporary solution I mentioned of the union query, isn't the optimal solution but it does get past the immediate problem and once the tables are properly normalized, the result will look just like the output of the union and therefore, whatever you do with that union, would work as the final solution also.

If you understand a 1-m relationship, you should be able to come up with the two tables that will turn the matrix into two lists. Think of an order (one side) and order details (many side). This is that same relationship which has been flattened so that each "detail" is a column in a flattened table rather than a row in a child table.

I'm pretty sure that you are not linking to the SAP BE. At least, no client I ever worked for would allow me to link to their sacred SAP tables. They always extracted data that I requested. Given that scenario, each export is probably handled as an import to a temp table. Rather than keeping these temp tables in the application's FE or BE, the best solution is to create a template which is sometimes called a "side-end". The import process, whether you do it using make table queries or delete/append queries bloats whatever database holds the tables and that requires frequent C&R operations. The cleaner solution is to copy the template database which has the tables defined along with indexes and relationships if appropriate and then import the downloaded data into the template. Every day/week/month/year/whatever that you do a new download, you copy the empty template to a local folder overlaying the earlier version and then run the queries that run the appends. You can even leave the linked tables in the template so you don't break your append queries. You can refresh the links if the actual file names change or if the file names remain the same, then you just run the append queries.

 

[June7]

LeoM, why no output shown for OMBIRD 02?

If UNION gets too slow, use expression in query or VBA function called by query.

Here is non-UNION workaround. Assumes only 9 frequency fields as shown in sample.

Query:

SELECT Table1.TASK_LIST_GRP, Table1.TASK_LIST_COUNTER,
Switch(Not IsNull([Y1]),"Y1", Not IsNull([M4]),"M4", Not IsNull([M3]),"M3", Not IsNull([M2]),"M2", Not IsNull([M1]),"M1",
       Not IsNull([W8]),"W8", Not IsNull([W3]),"W3", Not IsNull([W2]),"W2", Not IsNull([W1]),"W1", True,"NO PKG") AS MAX_FREQ,
Switch(Not IsNull([Y1]),9, Not IsNull([M4]),8, Not IsNull([M3]),7, Not IsNull([M2]),6, Not IsNull([M1]),5,
       Not IsNull([W8]),4, Not IsNull([W3]),3, Not IsNull([W2]),2, Not IsNull([W1]),1, True,0) AS MAXF
FROM Table1;

Query2:

SELECT DISTINCT Query1.TASK_LIST_GRP, Query1.TASK_LIST_COUNTER, Query1.MAX_FREQ
FROM Query1 INNER JOIN
   (SELECT TASK_LIST_GRP, TASK_LIST_COUNTER, Max(MAXF) AS MF FROM Query1 GROUP BY TASK_LIST_GRP, TASK_LIST_COUNTER) AS Q
ON Query1.TASK_LIST_GRP=Q.TASK_LIST_GRP AND Query1.TASK_LIST_COUNTER=Q.TASK_LIST_COUNTER AND Query1.MAXF=Q.MF;

 

[ebs17]

So upload an example database

Since this will probably take longer, I have written down my thoughts in the attached demo. It will be shown:
1) how I imagine a useful may-to-many relationship,
2) how to UNPIVOT the original table,
3) how the data in the resulting list is divided into the tables of the relationship, including determining the necessary keys for the link.
Steps 2) and 3) are provided as a code solution for repeatability.

Sub Create_Copie_tblFrqTasks()
    Dim db As DAO.Database
    Dim sSQL As String
    Dim bRet As Boolean
    
    Set db = CurrentDb
    ' tblOriginal to tmpList (matrix to list)
    bRet = PivotToList("tblOriginal", "tmpList", 3, "FrqName", "xValue", dbText, False, True)
    ' add new entries to tblTasks
    sSQL = "INSERT INTO tblTasks (tskGroup, tskCounter)" & _
        " SELECT DISTINCT S.tskGroup, S.tskCounter FROM tmpList AS S LEFT JOIN tblTasks AS T" & _
        " ON S.tskGroup = T.tskGroup AND S.tskCounter = T.tskCounter WHERE T.tskGroup IS NULL"
    db.Execute sSQL, dbFailOnError
    ' tmpList to copie of tblFrqTasks incl. generating the keys to relationships
    If TableExistsDAO(db, "tblFrqTasks2") Then
        sSQL = "DROP TABLE tblFrqTasks2"
        db.Execute sSQL, dbFailOnError
    End If
    sSQL = "SELECT * INTO tblFrqTasks2 FROM tblFrqTasks WHERE False"
    db.Execute sSQL, dbFailOnError
    sSQL = "INSERT INTO tblFrqTasks2 (Tsk_ID, Frq_ID, xValue)" & _
        " SELECT T.Tsk_ID, F.Frq_ID, S.xValue FROM tblFrequences AS F" & _
        " INNER JOIN (tblTasks AS T INNER JOIN tmpList AS S" & _
        " ON T.tskCounter = S.tskCounter AND T.tskGroup = S.tskGroup) ON F.FrqName = S.FrqName"
    db.Execute sSQL, dbFailOnError
End Sub

There is also a crosstab query that basically restores the original table, but in a more compact form (one record per group-counter combination).
The desired result query is of course also available.

 

[Galaxiom]

The first thing to do when confronted by denormalized data coming from somewhere you have no control is to normalize it.

 

[gemma-the-husky]

Generally speaking your original table has repeating groups, and loads of empty values. It can't be well structured and normalised. I presume the row order between the repeating groups is going to be significant, and that is completely wrong. With a database the row order is a priori not significant.

It's hard to understand the the column ticks for ref OMBIRD,1, particularly that the second row has no ticks.

The column ticks for Ref OMBPCRQ1 also look curious as well. Why do some rows have multiple ticks?

 

[LeoM]

Hello everyone, first of all i would like to thanks all those give support on this tread, it really help me to manage the issue, sorry for late answer bit could not before. I would like to give some answer to some of you:

To Pat: yes you are absolutely right, regardless i'm fighting to get direct link to SAP, at the end what i get is a set of view tables coming from an intermediate database managed by SAP Support who extract data from SAP two times a day. This is the maximum i obtain but at least is something. In another country where i worked i was more lucky, they give me the right to the tables.

To June7 : "why no output shown for OMBIRD 2". Sorry just because i sent another more small example to show the result of the union query which work fine. So i just remove it from the example, nothing more.

To gemma-the-husky:
- refer to "It's hard to understand the the column ticks for ref OMBIRD-1, particularly that the second row has no ticks." : yes this is a mistakes. Not sure if you are aware how it work a TL in SAP (but i think yes from your question) however, very simple, a TL has multiple Operation and each operation should have at least 1 packages (frequencies) or multiple. In this case is a mistake.
- refer to "The column ticks for Ref OMBPCRQ1 also look curious as well. Why do some rows have multiple ticks?" There are some frequencies (activities) that may be repeated that's why sometime each operation has multiple package. Then we may open a discussion about this way to do (for me wrong) but is not this the correct discussion.

Now that the problem is "temporary" solved, I will definitively work in the "normalization" of the table using your evaluable examples and suggestion.

Cheers,
Leo

 

[Gasman]

I had to ask our database supplier to provide data in an unnormalized format so that I could work with it in Access.
So they created a program whereby I would download a csv file and work with that, in the way that I wanted it.