Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 52
  1. #16
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    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.
    You could use VBA, but the question is 'How many records could there be for 1 customer?'.

    Using your Excel example, the basic company info (in Access) is 16 fields. Each record has is 28 fields for the details. With 4 records in the query, you use 128 fields. The max fields in 1 Access table is 254, so you would be able to have a max of 8 records per customer if you were to write the data to an Access table.

    If you were to write the data to a text file or Excel workbook, you could have more columns (fields), which means there could be more records (more than 8) in the main query.

  2. #17
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Steve; I appreciate your reply? I've been at this all day. You said that I could achieve this with VBA. I am currently getting rid of the duplicates. After this; each customer would have no more than 6 records. Is there different VBA code with the sample that I provided that can assist me you can share please. Thanks

  3. #18
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Are you still wanting to use an Access table? Or text file.... Excel???
    If Access, would you post your dB (with a few text records) so I know the field types?

    It might take me over the weekend to write it...... have to work around previous weekend plans.

  4. #19
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Ok. I'll get the db with the table together and post it. Thank u

  5. #20
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Please see attached DB. It is the tbl with samsamp.zipple data for 3 cust. Thanks so much for all your help.

  6. #21
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Question about "tblSchChg.RealRate"..... it is TEXT in the table. Shouldn't it be DOUBLE??

    I am going to change it to DOUBLE... tell me if should really be TEXT.

    Also, "Current_Boss" is a double. Should it be TEXT??


    In your Excel workbook, there is a column "BOSS" that is not in the table "tblSchChg"? Is this correct?
    Last edited by ssanfu; 01-13-2017 at 01:02 AM.

  7. #22
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    GM Steve, RealRate should be DOUBLE. Also; "Current_Boss" should be DOUBLE. It stands for Bossier Rate. The Boss field was incorrectly left out when I was copying the table structure for the sample file.
    Also; in reference to your question about how many records a customer may have depends how many different cust_codes the customer has. In scrubbing my data; I verified there are at least two customers having multiple "st_id" with at a max of 3 "cust_code" So for example; the last customer in the sample table cust 4251 with st_id FL can also have a st_id GA with different cust_codes. My sample did not include the different cust_code senario. You may change this for a more realistic outcome. I really appreciate your help.

  8. #23
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Difference between Cust and cust_code is Cust is the actual cust number cust_code is the customers location code the company uses.
    The records are grouped by "Cust". So if there are 5 records with a "cust" field is '1234', they are combined. There can be a max of 6 records. If you want to use "cust_code", let me know....

    Since you can't have multiple field names that are the same, I added a suffix of "_x" where x is 1 - 6 (number of records). "Boss" becomes "Boss_1" for the first record combined, "Boss_2" is from the 2nd record, ...


    Like in sample: cust_code 4321 have related fields in yellow and cust_code 2345 related values in blue and so on?
    Can't set/change colors in a table, but you could if you export the table to Excel.


    I must say, this seems like a strange request from the user


    Anyway, try the dB
    Please note that there might be/are a few differences form your Excel spreadsheet example...

    (I couldn't sleep last night.... all of a sudden it was 2:30am ....... 's ok.. I had my Pepsi and candy! )
    Attached Files Attached Files

  9. #24
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Steve;Thanks so much! My initial testing appears the data with the format is what the user requested. I agree with you that it is a strange request by the user. I asked and was told the file will be used for accounting purposes and the notify customers of a increase in their rates. Why they wanted everything in a single record beats me. I will test with the full data set and let you know how it goes. I did find the source of the duplicated records is because they use a union query to create the final tblschchg. So I will try to eliminate the duplicates before the table is create. Again; Thank you so much. btw; I can't imagine what grouping on the cust_code as well would look like whether it would make a huge difference. What do you think? would it be a big inconvenience to add this to see? Thanks

  10. #25
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by ssanfu View Post
    If you want to use "cust_code", let me know....
    Here I meant selecting unique source records from table "tblSchChg" using field "Cust" OR "cust_code". After thinking about it, you could use both fields if you thought it was necessary/required/better.


    Quote Originally Posted by slimjen View Post
    I did find the source of the duplicated records is because they use a union query to create the final tblschchg. So I will try to eliminate the duplicates before the table is create.
    If the record are determined to be duplicates based on the fields in the query "r", you could try adding the predicate "DISTINCT" to see if the duplicates are eliminated. It all depends on how the records are determined to be duplicate and if you want to spend the time looking for/eliminating the duplicates.


    Quote Originally Posted by slimjen View Post
    I can't imagine what grouping on the cust_code as well would look like whether it would make a huge difference. What do you think? would it be a big inconvenience to add this to see?
    I can make the changes of you can.

    You probably know how to do this, but these are the steps to modify the queries:
    1) Make a COPY of the database for a backup. <<-- VERY important!!
    2) Open the IDE
    3) Find the following code segment. Modify the code:
    (Comment out the lines with the RED, add the line with the BLUE)
    Code:
        'get unique "cust" records
        '             sSQL = "SELECT DISTINCT tblSchChg.Cust"
        sSQL = "SELECT DISTINCT tblSchChg.cust_code"
        sSQL = sSQL & " FROM tblSchChg;"
        Set s = d.OpenRecordset(sSQL)
        '    Debug.Print sSQL
    4) Then modify this code segment:
    Code:
                sSQL = "SELECT tblSchChg.RenDate, tblSchChg.Cust, tblSchChg.cust_id, tblSchChg.bus_name,"
                sSQL = sSQL & " tblSchChg.address1, tblSchChg.bus_name2, tblSchChg.address2,"
                sSQL = sSQL & " tblSchChg.address3, tblSchChg.city, tblSchChg.ST, tblSchChg.contact, tblSchChg.zip,"
                sSQL = sSQL & " tblSchChg.fax, tblSchChg.ID, tblSchChg.phone, tblSchChg.st_id,"
                sSQL = sSQL & " tblSchChg.st_id, tblSchChg.cust_code, tblSchChg.Current_Boss, tblSchChg.RealRATE,"
                sSQL = sSQL & " tblSchChg.Rate2, tblSchChg.Rate3, tblSchChg.Rate4, tblSchChg.Rate5, tblSchChg.Rate6,"
                sSQL = sSQL & " tblSchChg.Rate7, tblSchChg.Rate8, tblSchChg.Rate9, tblSchChg.Rate10, tblSchChg.Rate11,"
                sSQL = sSQL & " tblSchChg.Current_RealRATE, tblSchChg.CurrentRate2, tblSchChg.CurrentRate3, tblSchChg.CurrentRate4,"
                sSQL = sSQL & " tblSchChg.CurrentRate5, tblSchChg.CurrentRate6, tblSchChg.CurrentRate7,"
                sSQL = sSQL & " tblSchChg.CurrentRate8, tblSchChg.CurrentRate9, tblSchChg.CurrentRate10, tblSchChg.CurrentRate11"
                sSQL = sSQL & " FROM tblSchChg"
                '      sSQL = sSQL & " WHERE tblSchChg.Cust = '" & s!Cust & "';"
                sSQL = sSQL & " WHERE tblSchChg.Cust = '" & s!cust_code & "';"



    To try using code to eliminate the "duplicate records", try changing this line. Add the word in BLUE:
    Code:
                sSQL = "SELECT DISTINCT tblSchChg.RenDate, tblSchChg.Cust, tblSchChg.cust_id, tblSchChg.bus_name,"
                sSQL = sSQL & " tblSchChg.address1, tblSchChg.bus_name2, tblSchChg.address2,"
                sSQL = sSQL & " tblSchChg.address3, tblSchChg.city, tblSchChg.ST, tblSchChg.contact, tblSchChg.zip,"
                sSQL = sSQL & " tblSchChg.fax, tblSchChg.ID, tblSchChg.phone, tblSchChg.st_id,"
                sSQL = sSQL & " tblSchChg.st_id, tblSchChg.cust_code, tblSchChg.Current_Boss, tblSchChg.RealRATE,"
                sSQL = sSQL & " tblSchChg.Rate2, tblSchChg.Rate3, tblSchChg.Rate4, tblSchChg.Rate5, tblSchChg.Rate6,"
                sSQL = sSQL & " tblSchChg.Rate7, tblSchChg.Rate8, tblSchChg.Rate9, tblSchChg.Rate10, tblSchChg.Rate11,"
                sSQL = sSQL & " tblSchChg.Current_RealRATE, tblSchChg.CurrentRate2, tblSchChg.CurrentRate3, tblSchChg.CurrentRate4,"
                sSQL = sSQL & " tblSchChg.CurrentRate5, tblSchChg.CurrentRate6, tblSchChg.CurrentRate7,"
                sSQL = sSQL & " tblSchChg.CurrentRate8, tblSchChg.CurrentRate9, tblSchChg.CurrentRate10, tblSchChg.CurrentRate11"
                sSQL = sSQL & " FROM tblSchChg"
                sSQL = sSQL & " WHERE tblSchChg.Cust = '" & s!Cust & "';"
    I would create a query without the DISTINCT keyword to get a record count of all records, then add the DISTINCT keyword. Compare the record counts of the two queries. Then look at the 2nd query to see if the correct (duplicate) records are/were not included....
    (The Criteria, "Cust" or "cust_code", MUST be the same)


    And if you would rather that I make the changes, let me know..........

  11. #26
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thanks so much! I'll give it a go. I did try adding "Distinct" to the query with the dups (union) but still dups. I'll work on it. I'll let you know if I run into problems modifying the code. Thanks for all you help Steve.

  12. #27
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You're very welcome.....

  13. #28
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If the union query produces duplicates, check if it uses ALL. UNION ALL allows duplicates, UNION alone eliminates them.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #29
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Steve, I was running a test using live production data but after the first 12 customers, it threw an error "run time error 3134 syntax error in INSERT INTO statement. The output is correct with the exception of on customer that had 9 records which I didn't know about. It just didn't output the last three records. The user now says 1 customer can have up to twelve records. smh. Anyway, do you know why it stopped after 12 customers? The data I'm testing with has over 200 customers. It will always be about the same amount of customers. I didn't make changes to the code yet to test your last post.

  15. #30
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The combined table only has enough fields for combining 6 records. So if a customer has more than 6, I would expect the code to bomb - there are not enough fields to insert the data into.

    The maximum number of fields in a table is 255 (over the lifetime of the table). So if you plan for one customer having up to 12 sub records, that is 312, not counting the 16 common field. You would need 328 fields.


    If I recall correctly, I think you said you were going to export the combined records data to Excel. What do you think about writing the combined records to a text file (CSV), then import the CSV file into Excel??
    A lot of code changes but might be easier???

Page 2 of 4 FirstFirst 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