Results 1 to 6 of 6

How to set up a query to return 1A 2A IIA 1B 2B IIID etc three fields numbers groups & letters ?

  1. #1
    DBenz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    38

    How to set up a query to return 1A 2A IIA 1B 2B IIID etc three fields numbers groups & letters ?

    Hi,
    Access 2010.
    My title is somewhat simplified on my request.
    I have a number of fields, however whilst needing to show all fields per record, three fields are the fields of interest.
    first has a unit number 1 2 3 through to 9,
    the second has a group number I or II or III, 1 2 and 3 is in group I, 4 5 and 6 is in group II, 7 8 9 in group lll.
    the third has the larger group name (lets call this field 'LGN' ) and it holds A B C D E etc these exist in. Each letter will have groups I II and II in it, thus each letter holds 9 units nos 1 to 9

    A record could be 5 A which by being known also means its group II, or it might be II A if the unit is unknown but the group is known. I would fill in unit and group if the unit was known.

    so records can be:-
    1 A
    II A
    1 G
    3 H
    III D
    5 B
    7 A
    7 D
    2 A
    3 J
    7 H
    I need to put the records into the order, whereby I deal with LGN A followed by B then C, and units in number order, with Group I showing after 1 2 and 3 have shown, II showing after 4 5 and 6 have shown and so on , so it would be looking like the order :-
    1 A
    2 A
    II A
    7 A
    5 B
    7 D
    III D
    1 G
    3 H
    7 H
    3 J



    how would I design the query which would make a new form show these in this order ?

    Cheers

    DBenz

  2. #2
    Gicu's Avatar
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    713
    Care to show your pertinent table design with some sample data? Do you leave the "unknown" fields empty? Are all three in the same table? I would say the right orderby sequence along with maybe a calculated field to fill in the "unknowns" should do it, but can't really say with just the info provided.

    Cheers,
    Vlad

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,579
    would have though you could use the choose and isnumeric functions to make this work. something like

    ORDER BY iif(isnumeric(F1),choose(F1,"I","I","I","II","II", "II","III","III","III"),F1), LGN

  4. #4
    DBenz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    38
    Hi,
    attached a sample table and form of those sample values mentioned, unknown fields are left empty. I havent bothered with the other fields, make some up if you need to, maybe place names for one field, dates for another etc, peoples names for another.
    If for example I know a record is 3 H i would enter 3 I H though for reports i need only to show 3 H as 3 I H isn't the correct way of displaying that data.

    If someone could create the query that displays these in a form as per my mockup in post#1 that would be great so that as I go through the form record by record they display as per my list in post#1.

    This is going to be something I have longed to do, and been puzzled by.

    DBenz
    Test_Unit_Group_LGN.accdb

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,579
    sorry, don't have time to get any more involved. Perhaps someone else would like to make a suggestion.

    You said what you wanted, I provided a solution based on the limited data provided. You have not said whether that works or not for you but instead seem to be wanting something else, and for someone to do it for you.

    good luck with your project

  6. #6
    Gicu's Avatar
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    713
    Is this your looking for? You need to simply sort your fields in the right order, unless I completely missed the point...

    Cheers,
    Vlad
    Attached Files Attached Files

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

Similar Threads

  1. Replacing Numbers with Letters
    By Darth_Elicious in forum Queries
    Replies: 7
    Last Post: 04-13-2017, 01:55 PM
  2. mix of letters and numbers on the form
    By iscinar in forum Forms
    Replies: 15
    Last Post: 10-20-2013, 05:11 AM
  3. Add letters to sequential numbers
    By bassplayer79 in forum Programming
    Replies: 29
    Last Post: 09-12-2013, 03:20 AM
  4. IIF with numbers and letters
    By hzrdc2 in forum Queries
    Replies: 2
    Last Post: 03-28-2013, 06:29 AM
  5. Import Errors - Fields with Numbers and Letters
    By Eekers in forum Import/Export Data
    Replies: 5
    Last Post: 01-10-2012, 01:52 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
  •  
Tech Forums: Microsoft Office Forums