Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine Two Records Into One? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Is there a way to construct an INTERJOIN query to connect the first two records in a table as the first query record returned, the second two records in the table as the second query record returned, etc

    Example, using the NW Customers table the query would return:

    Customer ID -- Company Name -- Contact Name -- Customer ID -- Company Name -- Contact Name
    ALFKI Alfreds Futterkiste Maria Anders ANATR Ana Trujillo Emparedados y helados Ana Trujillo
    ANTONAntonio Moreno Taquer

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine Two Records Into One? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    You need a way to number records, and return fields from the odd-numbered records (1, 3, 5, ...) together with fields from the next record (2, 4, 6, ...). The only ways I can think of are EXTREMELY slow. I'm curious to see if others have a better idea.

    1. Create a query based on the Customers table that adds a line number. I used DCount for this; it is also possible to use a custom VBA function for this. SQL:

    SELECT Customers.*, DCount("*","Customers","CustomerID<=" & Chr(34) & [CustomerID] & Chr(34)) AS LN
    FROM Customers
    ORDER BY CustomerID

    LN stands for Line Number. Save this as qryCustomerNumber.

    2. Create a query based on two copies of qryCustomerNumber. Select the odd-numbered records from the first copy by requiring that [LN] Mod 2 equals 1. We will use a non-standard join between the tables, to make the second copy return the record following the one of the first copy. This query cannot be displayed in design view, only in SQL view and in datasheet view. SQL:

    SELECT qryCustomerNumber.CustomerID, qryCustomerNumber.CompanyName, qryCustomerNumber.ContactName, qryCustomerNumber_1.CustomerID, qryCustomerNumber_1.CompanyName, qryCustomerNumber_1.ContactName
    FROM qryCustomerNumber INNER JOIN qryCustomerNumber AS qryCustomerNumber_1 ON (qryCustomerNumber.LN+1) = qryCustomerNumber_1.LN
    WHERE ([qryCustomerNumber].[LN] Mod 2)=1

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine Two Records Into One? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Perfect

    Thanks, John

    <I'm curious to see if others have a better idea.>

    Me too

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine Two Records Into One? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Hi Hans

    Check out qry_Customer_Combine_John_Mishefske in attached db that I got from another forum.

    Only problem, I have 91 records coming in and 45 records in the query, missing to odd 91st record.

    Any thoughts?

    Thanks, John
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine Two Records Into One? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    John, I see that you added an AutoNumber field ID to the Customers table. The thought had occurred to me too, but if you ever delete records from the table, there will be gaps in the sequence of values of ID, disprupting the query.

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine Two Records Into One? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Good observation

    This query is created once a year for input to a report.

    If I use this method I will have to create a temp table on the fly with a autonumber

    What would I have to change to grab all the fields from Customers table?

    SELECT [qryCustomerNumber].[CustomerID], [qryCustomerNumber].[CompanyName], [qryCustomerNumber].[ContactName], qryCustomerNumber_1.CustomerID, qryCustomerNumber_1.CompanyName, qryCustomerNumber_1.ContactName
    FROM qryCustomerNumber INNER JOIN qryCustomerNumber AS qryCustomerNumber_1 ON ([qryCustomerNumber].[LN]+1)=qryCustomerNumber_1.LN
    WHERE ([qryCustomerNumber].[LN] Mod 2)=1;


    John

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine Two Records Into One? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Replace INNER JOIN by LEFT JOIN.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •