Distinct on a query

[mloucel]

Hello GURUS:

I am building sort of a complex query [for me "newbie"] and this is what I need to accomplish:

The query should give me all the records while they are not marked CANCELED, the caveat is that 1 of the tables contains Notes for that record, they can be 1, 2 or 3 notes, for the purpose of this particular form I do not care how many notes there are, I am happy to see the first note only, then when the user picks the record a different form will pop up, showing that particular record with all bells and whistles.
The table for notes has the following structure:
PnoteID - AutoNumber
AuthorizationID - FK to the main database
PNoteDate - date the note was entered
PNote - note attached [Long Text]

This is the SQL created by Access, the DISTINCT clause was added by me trying to make it work, but as you can infer I am not good at all.
Or if you know of any other way to RE_DO this SQL then please correct by any means.

ANY help will be appreciated.

Thank you.

SELECT DISTINCT AuthorizationNotesT.authorizationID, AuthorizationT.AuthorizationID, UserT.UserName, AuthorizationT.ReferDate, [plastname] & ", " & [pfirstname] AS Name, PatientT.PDOB, PatientT.PPhone, IpaT.IpaName, HealthPlanT.HealthPlan, AuthorizationT.Diagnosis, DoctorT.DocName, SpecialtyT.Specialty, SpecialistT.DrName, SpecialistT.DrPhone, AuthorizationT.Urgent, AuthorizationT.AuthDateEntered, AuthorizationT.ApprovedDate, AuthorizationT.AppointmentDate, AuthorizationT.MedicalRecordSendDate, AuthorizationT.PatientNotifiedDate, AuthorizationT.ReportFileDate, AuthorizationT.DeniedDate, AuthorizationT.ModifiedDate, AuthorizationNotesT.PNoteDate, AuthorizationNotesT.PNote
FROM UserT INNER JOIN ((SpecialtyT INNER JOIN SpecialistT ON SpecialtyT.SpecialtyID = SpecialistT.SpecialtyID) INNER JOIN (PatientT INNER JOIN (IpaT INNER JOIN (HealthPlanT INNER JOIN (DoctorT INNER JOIN (AuthorizationT INNER JOIN AuthorizationNotesT ON AuthorizationT.AuthorizationID = AuthorizationNotesT.AuthorizationID) ON DoctorT.DoctorID = AuthorizationT.DoctorID) ON HealthPlanT.HealthPlanID = AuthorizationT.HealthPlanID) ON IpaT.IpaID = AuthorizationT.IpaID) ON PatientT.PatientID = AuthorizationT.PatientID) ON (SpecialtyT.SpecialtyID = AuthorizationT.SpecialtyID) AND (SpecialistT.SpecialistsID = AuthorizationT.SpecialistID)) ON UserT.UserID = AuthorizationT.UserID
WHERE (((AuthorizationT.AuthCanceled)=False))
ORDER BY AuthorizationT.AuthorizationID;

 

[theDBguy]

One possible approach is to use a query with a subquery to select the top 1 per group and the inner join that to your main table rather than the notes table itself.

 

[Pat Hartman]

You didn't specify what was wrong with the results of the query. Error, missing records, duplicate records - are only three of the possible issues.
So, I'll take a stab in the dark:

Remove the Distinct keyword since that will make the form not updateable.
Remove the notes table from the join. The Notes need to be in a subform and we are working with the query for the main form. If there are other many-side tables in the query, remove them also and use subforms to show their data.

 

[plog]

Agree with Pat, but I'd go further--start a brand new query with just AuthorizationT. Use this:

SELECT AuthorizationID 
FROM AuthorizationT
WHERE AuthorizationT.AuthCanceled=False

Does that have the right records? If so bring all the fields you need from it, JOIN another table and check again. Wash, rinse, repeat

 

[mloucel]

You didn't specify what was wrong with the results of the query. Error, missing records, duplicate records - are only three of the possible issues.
So, I'll take a stab in the dark:

Remove the Distinct keyword since that will make the form not updateable.
Remove the notes table from the join. The Notes need to be in a subform and we are working with the query for the main form. If there are other many-side tables in the query, remove them also and use subforms to show their data.

Thanks Pat, sorry, the issue is that since the notes may have more than 1 note in one day, the record ID will be always different [which is correct and should be like that, is the record KEY], the date is given on the same day will be always the same date, unless the note is recorded in a different date, and the note will be different ALWAYS, so the user can say whatever he needs [Just imagine a notebook, where you record your own notes, the first thing is the date, then the note, next line same date, different note, then you comeback 3 days later, you add a different date and another note], hence the issue, when I run the query, [as example] it runs fine, as long as there is only 1 note, if there are 2 or 3 notes, then shows 2 or 3 times the NoteID with the corresponding note, which in theory is correct, except that for my purpose, creates 2 or 3 records on the form, I DO NOT NEED THAT MANY. I JUST NEED TO DISPLAY WHATEVER IS ON THE FIRST.
Now to make this more specific, this is a 1 Single Continuous Form, the idea of this form is to display every single data [needed] so that the user can make decisions based on what is displayed in the screen, of course the main table only keeps the FK of each record, so in this particular form I use ALL the tables, so that I display the NAMES instead of the ID's, [ Display Category Name instead of CategoryID ] so the query helps me do all the hard work and I just need to make the RECORD SOURCE the query that contains already the data as I needed, saves time, and I don't have to deal with anything, just display the data as retrieved by the query, this is FOR DISPLAY PURPOSES ONLY, there is no pick and do something else, the user only needs to see ALL the data in 1 line and take a decision based on what he sees there.
But displaying 2 or 3 lines of the same record will be confusing to the user.
I am not using any subforms, this will not be a subform nor ever will be, this will be a SINGLE CONTINUOUS FORM use.

I hope this lengthy explanation clarifies what I want, and the reason why I need the Query the way it is, now, please remember that thou I have been programming, I am still learning, there might be a better way to create this continuous form, I just know this way with a query, I just don't know how to make the query so that no matter how many notes are in the record I only display the first NOTE and ignore the other ones.
I thought DISTINCT was going to do the trick but as well this is my FIRST time trying to do something like this so I am in Learning territory here.

Here is a picture of the main table AuthorizationT and the table in question AuthorizationsNotesT
Maybe this will make the explanation a bit better.

Thanks.

 

[Pat Hartman]

When you join to a many-side table, you are always going to get many rows where the many-side table has more than one. the solution involves a left join to the notes table AND a sub query that selects the first note record only. If you don't know how to do subselects, you can create a querydef that selects the first record from the notes table for each AuthorizationID

Select AuthorizationID, First(PNote) as FirstNote, First(PnoteDate) as FirstDate
From AuthorizationNotesT
Group by AuthorizationID

Then instead of including the notes table in your query, include this saved querydef and join to it using a left join because not all Authorization records will have a note.

 

[mloucel]

When you join to a many-side table, you are always going to get many rows where the many-side table has more than one. the solution involves a left join to the notes table AND a sub query that selects the first note record only. If you don't know how to do subselects, you can create a querydef that selects the first record from the notes table for each AuthorizationID

Select AuthorizationID, First(PNote) as FirstNote, First(PnoteDate) as FirstDate
From AuthorizationNotesT
Group by AuthorizationID

Then instead of including the notes table in your query, include this saved querydef and join to it using a left join because not all Authorization records will have a note.

Thanks Pat, I have never done anything like you suggest so I am going to investigate first to make sure I do understand.

 

[mloucel]

When you join to a many-side table, you are always going to get many rows where the many-side table has more than one. the solution involves a left join to the notes table AND a sub query that selects the first note record only. If you don't know how to do subselects, you can create a querydef that selects the first record from the notes table for each AuthorizationID

Select AuthorizationID, First(PNote) as FirstNote, First(PnoteDate) as FirstDate
From AuthorizationNotesT
Group by AuthorizationID

Then instead of including the notes table in your query, include this saved querydef and join to it using a left join because not all Authorization records will have a note.

Hello Pat, I was following instructions from:
This YouTube page
it works to create the sub, but I need to incorporate the PNoteID, so that when I add the subquery to the query I have [Minus the notes table], I have a FK and a PK to relate.
when I add the PnoteID to the subquery I got the error:

but if I add PNoteID as part of the aggregate function, then I get all the values, the FIRST is ignored.

I don't know how to solve this.

 

[Pat Hartman]

but if I add PNoteID as part of the aggregate function, then I get all the values, the FIRST is ignored.

That is why you need the "first" to be either a second separate query or a subquery within the main query. The QBE does not have a good graphical interface to create subqueries so I only use them when there is no alternative. In this case, you should be able to create a totally separate querydef to retrieve the First() note for each main record. Once the query works, save it. Then go back to your original query and replace the join to the notes table with a join to the new "totals" query. You also must change the join type from INNER to LEFT. To do that, double click on the join line. You will be presented with 3 options. Read them carefully and choose the one that will return all the records from the main table and matching records from the query. This will almost always be the "left" option but it depends on how you built the query initially and how you drew the relationships. So, it might be the "right" option.

Inner joins match all the rows from each table and return a row for each match. So, if the two tables/queries each contain 100 rows with a 100% correlation, then the recordset will be 100 rows. If only 50 of the rows exist in both tables/queries, then the recordset will contain 50 rows. If one table contains 100 records and the other contains 250 but the other has 0, 1, or 3 matches, I can't even predict what the row count of the recordset will be but, some of the 100 records in the first table will be missing if the second table has no matches and some of the records will be duplicated if the second table contains 2 or 3 matches.

Left joins (or right joins) return ALL the records from the left (or right) table/query and only matching rows from the second table/query

 

[DickyP]

Not quite applicable to this thread but there is one gotcha with DISTINCT: the normal default is ALL but on UNION queries the default is DISTINCT.

 

[mloucel]

That is why you need the "first" to be either a second separate query or a subquery within the main query. The QBE does not have a good graphical interface to create subqueries so I only use them when there is no alternative. In this case, you should be able to create a totally separate querydef to retrieve the First() note for each main record. Once the query works, save it. Then go back to your original query and replace the join to the notes table with a join to the new "totals" query. You also must change the join type from INNER to LEFT. To do that, double click on the join line. You will be presented with 3 options. Read them carefully and choose the one that will return all the records from the main table and matching records from the query. This will almost always be the "left" option but it depends on how you built the query initially and how you drew the relationships. So, it might be the "right" option.

Inner joins match all the rows from each table and return a row for each match. So, if the two tables/queries each contain 100 rows with a 100% correlation, then the recordset will be 100 rows. If only 50 of the rows exist in both tables/queries, then the recordset will contain 50 rows. If one table contains 100 records and the other contains 250 but the other has 0, 1, or 3 matches, I can't even predict what the row count of the recordset will be but, some of the 100 records in the first table will be missing if the second table has no matches and some of the records will be duplicated if the second table contains 2 or 3 matches.

Left joins (or right joins) return ALL the records from the left (or right) table/query and only matching rows from the second table/query

Good Morning/afternoon/evening @Pat Hartman , Is there any place I can see an example of this, I have to be honest, I have no idea how to accomplish that, my knowledge does not go that far, I am lost.
I rather be a dumb once and honestly ask, than trying to appear to know what I don't.
Maurice.

 

[mike60smart]

Hi Maurice
Are you able to upload a copy of the database with no confidential data?

 

[mloucel]

Hi Maurice
Are you able to upload a copy of the database with no confidential data?

There is no data, the DB is under construction, so all data is fake.
I'll zip and see how it goes.

 

[mloucel]

When you join to a many-side table, you are always going to get many rows where the many-side table has more than one. the solution involves a left join to the notes table AND a sub query that selects the first note record only. If you don't know how to do subselects, you can create a querydef that selects the first record from the notes table for each AuthorizationID

Select AuthorizationID, First(PNote) as FirstNote, First(PnoteDate) as FirstDate
From AuthorizationNotesT
Group by AuthorizationID

Then instead of including the notes table in your query, include this saved querydef and join to it using a left join because not all Authorization records will have a note.

Pat...
I'm an I.D.I.O.T.
I found out my issue, I think way more than I should, I finally understood what is it that you explain plain and simple to me, ME on the other side complicated things by NOT understanding what it was presented to me in the code, by adding the PNoteID to the SQL, I was adding something I didn't need at all, totally worthless, just added more problems, when the solution was there right in front of me all the time.

I also didn't know that I can add a query on top of a query, hence more confusion to me, now I know.

Thank you so much for taking the time guiding me, I was wrong.

my utmost respect to you and for your patience.

Maurice.

 

[Pat Hartman]

@mloucel There probably isn't a week that goes by that I don't learn something new. Isn't it exciting? I'm glad you were able to follow my instructions to create the second query.

I attached a one page summary of joins that I hope you will find useful. You might want to print it out and hang it on the wall behind your desk, it is that important for your learning. It has Venn diagrams (Kamala's favorite) that hopefully clarify what data is returned by each join type. Notice that the first join type is Inner since that is the most common. It returns only records in the intersection part of the diagram. So the join key needs to be in BOTH tables to return a row.

You had a slightly different problem because you wanted to ensure that your query returned only a single row for each row in tableA regardless of whether or not there were 0 or many in tableB. To handle that, I suggested a separate query that summarized tableB to a max of one row for each FK and selected only the first() values. First() is a nebulous concept since it is affected by table order. But using Max() or Min() which are more common, could return values from different records and we need the values from several fields from the SAME record and First() does that.

 

[mloucel]

@mloucel There probably isn't a week that goes by that I don't learn something new. Isn't it exciting? I'm glad you were able to follow my instructions to create the second query.

I attached a one page summary of joins that I hope you will find useful. You might want to print it out and hang it on the wall behind your desk, it is that important for your learning. It has Venn diagrams (Kamala's favorite) that hopefully clarify what data is returned by each join type. Notice that the first join type is Inner since that is the most common. It returns only records in the intersection part of the diagram. So the join key needs to be in BOTH tables to return a row.

You had a slightly different problem because you wanted to ensure that your query returned only a single row for each row in tableA regardless of whether or not there were 0 or many in tableB. To handle that, I suggested a separate query that summarized tableB to a max of one row for each FK and selected only the first() values. First() is a nebulous concept since it is affected by table order. But using Max() or Min() which are more common, could return values from different records and we need the values from several fields from the SAME record and First() does that.

WOW, this is priceless info Pat.. I have no words to thank you for being so kind and above all PATIENT [Pun intended] with me, I have much to learn about everything, and getting rid of OLD programming style [Cobol, dBase, FoxBase, etc.] is really hard, specially after 30 years of not doing any programming, which now is called "CODING".
Your explanation is simple and straight, and your documentation as help is superb, I am simply lucky, not like Kamala's today is the yesterday's tomorrow's yesterday speech [] , also I would like to thank @mike60smart who even did a conference call with me, and help me understand concepts I just simply didn't know at all.

Thank you for helping the rest of us simple human beings you mighty GURUS.

Maurice.

 

[Pat Hartman]

You're very welcome. The one-page summary came from a class I prepared for some data analysts who worked with Excel and their management wanted me to teach them how to use queries at a minimum to pare down the multi-million row recordsets they were working with. Just doing that cut a minimum of an hour of down time (waiting to calculate) out of their work day because they could aggregate and do joins to eliminate the VLookup()s their models were using. Two of the guys transferred a lot of his model's processing to Access because it was easier than using Excel.

The hardest transition from COBOL for me was getting the hang of event processing. VBA is similar in concept to COBOL unlike many other languages which are much more cryptic. So, the hardest thing about that was to stop ending every command with a period COBOL still does tables (arrays) better than VBA and when I left COBOL, it hadn't yet acquired the concept of functions which are enormously useful but which I believe it currently supports. I was also lucky because the early versions of Access actually came with language manuals so you had an orderly overview of the structure of the language. Those are long gone and the web documentation is seriously inferior even though it is easier to use. Alpha sequence is useless if you don't know what you are looking for but the children don't seem to grasp that concept.

Here's some other documentation to keep handy if I haven't posted it for you before. This is a list of all the Access VBA functions by category. So if you are looking for a date function, look in the date category. The alpha list is useless if you don't know the name of the function you need. Simply reviewing the function list will help you going forward.

Access Functions (by category) - Microsoft Support

Find Help with Access functions in this categorical list.

support.microsoft.com

 

[DickyP]

COBOL ..

I loved reading how your transition to VBA from COBOL involved getting used to a different mindset. My start point in professional programming was Pl/I, which did have functions, very powerful arrays and we used an event controlled model for our online systems (albeit, of course character based). VB and then VBA was a pleasant catch up to all things I had missed in the intervening period, havinging to cope with the idiocies of C and C++, and the verbosity of Ada. I'm still waiting for decimal data types and manipurable pointers. As Bill Gates said when MS went to .net, C# is VBA for snobs.

 

[Pat Hartman]

Bill was a pretty smart cookie. Then we found out he was woke and a pervert with a reserved seat on the Lolita Express who thinks we should all eat bugs but not him.

 

[mloucel]

You're very welcome. The one-page summary came from a class I prepared for some data analysts who worked with Excel and their management wanted me to teach them how to use queries at a minimum to pare down the multi-million row recordsets they were working with. Just doing that cut a minimum of an hour of down time (waiting to calculate) out of their work day because they could aggregate and do joins to eliminate the VLookup()s their models were using. Two of the guys transferred a lot of his model's processing to Access because it was easier than using Excel.

The hardest transition from COBOL for me was getting the hang of event processing. VBA is similar in concept to COBOL unlike many other languages which are much more cryptic. So, the hardest thing about that was to stop ending every command with a period COBOL still does tables (arrays) better than VBA and when I left COBOL, it hadn't yet acquired the concept of functions which are enormously useful but which I believe it currently supports. I was also lucky because the early versions of Access actually came with language manuals so you had an orderly overview of the structure of the language. Those are long gone and the web documentation is seriously inferior even though it is easier to use. Alpha sequence is useless if you don't know what you are looking for but the children don't seem to grasp that concept.

Here's some other documentation to keep handy if I haven't posted it for you before. This is a list of all the Access VBA functions by category. So if you are looking for a date function, look in the date category. The alpha list is useless if you don't know the name of the function you need. Simply reviewing the function list will help you going forward.

Access Functions (by category) - Microsoft Support

Find Help with Access functions in this categorical list.

support.microsoft.com

Pat, thank you for your time, I hope that God can re-pay you back hundredfold.