Page 1 of 4 1234 LastLast
Results 1 to 15 of 52
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    convert records into one line


    All, using access 2016. A user wants a records in a query converted to one line by on cust. Essentially; they want all records related to a particular cust on one line. I've attached an excel dump of a sample of the query and the sample record on one line the way the user wants it. I changed the data to dummy data manually so I may have missed some number to the correct field. I have never done this. Can someone help please.
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    It sounds like it. I'll try it and report back. Thank you for helping.

  4. #4
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    btw; if it doesn't work, any other suggestions? I may be up late into the night. thanks

  5. #5
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I tried this code and it puts all the related results into one field. I need the results to stay in their respective fields but all fields related to the cust all on one line not in the same field. How do I accomplish this please.

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Not sure you can do it with queries from the fields you listed, maybe do it with VBA. What is difference between Cust and cust_code fields and what is the difference between those first 2 records? Will a Cust always have 4 records or could they vary?

  7. #7
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    thanks replying. Difference between Cust and cust_code is Cust is the actual cust number cust_code is the customers location code the company uses. there is also a cust_id if customers have mult accts. Company's setup not mine. The cust will have multiple rows depending on how many cust codes they have. The reason for the multiple rows is because you have two or more cust codes for each cust. So the multiple cust codes with related data is driving the multiple rows and I don't know have to get into one line. I am not oppose to VBA code in the effort to obtain the results needed.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is the goal to return values in Access or Excel? I can envision VBA code to walk through the data and place values into Excel. I suppose the same could be used to populate a temp table. Either way, it would be...messy. Unless I'm missing an easier solution.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Return values in Access. It can also be used to create another table I guess because ultimately I think they want to use a report for the final output. I am willing to get messy to get the results to the user and get this off my plate.

  10. #10
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Even the VBA route would be hard as you don't know how many records/fields a customer will have so can't create a table structure to hold all of that unless maybe you create a table somehow dynamically? Unless you know you will never have more then say 4 locations for a customer, then you could create up to 4 sets of fields for each possible record and only populate as needed.

    In your sample if the data is accurate(you said it might not be?),looks like first 2 records are for cust code 4321 and next 2 are 2345 so you want all 4 of those records into 1 record? But didn't you say different cust codes are for diff locations so how do you know which address and other values to use for the 1 record?

    As Pbaldy stated, I would imagine they either want a report or toexport the data? If they want a report in Access, could you not just create that and group by the Cust value to get those records in 1 section. Maybe create header groups for Cust Code if needed.

  11. #11
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I agree. Just spoke to the user and eventually it would be exported to excel. And you're correct; even if I assign a headed to the different cust codes, the related records to those cust codes would have to be identified correct? Like in sample: cust_code 4321 have related fields in yellow and cust_code 2345 related values in blue and so on?

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you say records converted to query - so your source data is a table? If so, do you have a unique ID? if you do - perhaps create a crosstab query to return the PK values in a row

    qryXTab
    TRANSFORM First(PK) AS PKnum
    SELECT Cust
    FROM myTable
    WHERE CUST=1234
    GROUP BY Cust
    PIVOT "PK" & PK

    then link to the table multiple times

    SELECT * 'change this to include the fields required in the order required
    FROM qrXTab q INNER JOIN myTable T1 on q.PK1=T1.PK INNER JOIN myTable T2 ON q.PK2=T2.PK...etc


    but no idea how you decide which address to use - something to discuss with your user

  13. #13
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    That's the issue I think, I don't see in the sample data a unique value to key on.

    "I changed the data to dummy data manually so I may have missed some number to the correct field."
    Can you verify your sample data(the records at the top, not your output), the fields and what is in them. You can change the values but make sure they relate the same way as the real data.

  14. #14
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I added the PK to the table today when I realized I needed it to start trying to figure this out. I created a query to add a field to count the number of cust codes each cust has. Then I created a crosstab query as suggested early. In adding back the fields a little at a time; I didn't run into an issue until I start adding back the Rate fields. The addresses were not a problem in the sample data but I think it will for the real data. Obviously I need a way to tie the cust codes even with the numbers as we thought. Also they're were duplicates in the data which may be compounding the issue. I really don't know where to go from here

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Also they're were duplicates in the data which may be compounding the issue
    by duplicates do you mean complete duplicates of the required fields for the final output or just some of them? also does the duplicate include the PK? You can use the Find Duplicates query wizard to identify where you have duplicates and perhaps eliminate them.

    If just the required fields, including the PK, you can use SELECT DISTINCT PK, fld1, fld2 etc

    otherwise you would use a group by query, selecting the first PK and grouping the rest of the fields

    or if some of the required fields are not duplicates you could use first for them as well.

    Where you have inconsistences you need to make a decision so either consult with your user to discuss and agree the 'rules' to be applied to handle them, or make you own mind up and tell the user what you have done.

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-04-2015, 09:08 PM
  2. Replies: 2
    Last Post: 10-23-2014, 12:10 PM
  3. Mutiple Records on a Single Line?
    By RMCook in forum Queries
    Replies: 1
    Last Post: 02-20-2014, 02:29 PM
  4. Command line to update records
    By Richard B in forum Programming
    Replies: 3
    Last Post: 10-01-2013, 06:03 AM
  5. Replies: 2
    Last Post: 11-19-2012, 01:00 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