Results 1 to 4 of 4
  1. #1
    diegomarino is online now Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    298

    Subquery

    hi, i have this query

    Code:
    SELECT OcfDettaglio.ID, OcfDettaglio.Nome, OcfDettaglio.Cognome, OcfDettaglio.Data_nascita, OcfDettaglio.DENOMINAZIONE_SOGGETTO_ABILITATO, OcfDettaglio.data_inizio, OcfDettaglio.Data_fine, [last bank] AS Espr1FROM OcfDettaglio
    WHERE (((OcfDettaglio.DENOMINAZIONE_SOGGETTO_ABILITATO) Like "*chebanca*") AND ((OcfDettaglio.data_inizio) Is Not Null))
    ORDER BY OcfDettaglio.ID;
    as you see, i want all records with "data_inizio" not null (it is a start date) and "DENOMINAZIONE_SOGGETTO_ABILITATO" Like chebanca; this gives me all the people that left their precedent bank and went to chebanca.
    i want the name of the precedent bank and the end date, the structure of the data is that

    COLUMNS
    "nome","DENOMINAZIONE_SOGGETTO_ABILITATO","start_d ate","end_date"

    DATAS
    BILL CARSON, PEPPAPIG BANK, ,13-1-2022
    BILL CARSON, CHEBANCA, 14-1-2022,




    With this query i get just the second row, i want to add a field called maybe "last bank" that gives me "peppapig bank".

    consider that there are sometime more than two rows per person, so i should take just the last bank before "chebanca"

    i think i need a subquery, but i'm not an expert of that

  2. #2
    diegomarino is online now Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    298
    or maybe a dlookup?

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    3,227
    Create a totals query grouped by Person (Last, First,DOB) and get Max(data_inizio) and Where [DENOMINAZIONE_SOGGETTO_ABILITATO] Not Like "*chebanca*". Now create a new query sorted descending by data_inizio and join it to the first one on the name and DOB fields and data_inizio to MaxOfdata_inizio. This one would give you the PREVIOUS_BANK which you can now retrieve by joining it into your original query. You can also use a subquery with a TOP 1 clause but I think this is clearer and easier to follow.

    https://support.microsoft.com/en-us/office/find-the-records-with-the-top-or-bottom-values-in-a-group-or-field-379ffdae-9869-4971-9c32-3d0bd1a95b07

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    diegomarino is online now Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    298
    sorry, i can't do that. Consider in first that the "id" field is not unique (i'll add a one later) and it a unique code for the person, so i can join the two query by that
    i show the queries
    query1
    Code:
    SELECT OcfDettaglio.ID, OcfDettaglio.Nome, OcfDettaglio.Cognome, Max(OcfDettaglio.data_inizio) AS MaxDidata_inizio, Last(OcfDettaglio.DENOMINAZIONE_SOGGETTO_ABILITATO) AS UltimoDiDENOMINAZIONE_SOGGETTO_ABILITATOFROM OcfDettaglio
    GROUP BY OcfDettaglio.ID, OcfDettaglio.Nome, OcfDettaglio.Cognome
    HAVING (((Last(OcfDettaglio.DENOMINAZIONE_SOGGETTO_ABILITATO)) Not Like "*chebanca*"));
    query 2

    Code:
    SELECT OcfDettaglio.ID, OcfDettaglio.DENOMINAZIONE_SOGGETTO_ABILITATO, OcfDettaglio.data_inizio, OcfDettaglio.DENOMINAZIONE_SOGGETTO_ABILITATOFROM qryUltimaBanca INNER JOIN OcfDettaglio ON (qryUltimaBanca.MaxDidata_inizio = OcfDettaglio.data_inizio) AND (qryUltimaBanca.UltimoDiDENOMINAZIONE_SOGGETTO_ABILITATO = OcfDettaglio.DENOMINAZIONE_SOGGETTO_ABILITATO) AND (qryUltimaBanca.ID = OcfDettaglio.ID)
    ORDER BY OcfDettaglio.data_inizio DESC;

Please reply to this thread with any new information or opinions.

Similar Threads

  1. A subquery inside a subquery
    By tbjmobile@gmail.com in forum Queries
    Replies: 15
    Last Post: 04-22-2018, 11:48 AM
  2. Subquery Help
    By MTSPEER in forum Queries
    Replies: 1
    Last Post: 12-05-2017, 01:57 PM
  3. Subquery
    By tomclavil in forum Queries
    Replies: 3
    Last Post: 02-27-2012, 03:05 AM
  4. TOP subquery
    By helpaccess in forum Queries
    Replies: 5
    Last Post: 08-30-2011, 10:28 AM
  5. Subquery
    By combine21 in forum Queries
    Replies: 2
    Last Post: 09-17-2010, 04:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums