Results 1 to 9 of 9

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
    69

    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
    Location
    Kelowna, BC, Canada
    Posts
    840
    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
    7,082
    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
    69
    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
    7,082
    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
    Location
    Kelowna, BC, Canada
    Posts
    840
    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

  7. #7
    DBenz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    69
    Hi,
    Vlad, thanks for the returned database file.
    Is this your looking for? You need to simply sort your fields in the right order, unless I completely missed the point..
    1 A
    2 A
    II A
    7 A
    5 B
    7 D
    III D
    1 G
    3 H
    7 H
    3 J
    I J
    5 J
    6 J
    6J
    II J
    II J
    IIJ
    7 J
    7 J
    8 J
    9 J
    9 J
    III J
    III J
    III J
    Above as mentioned is what the result would look like, with some more progression at J since I posted that example to further show how it would look, where we see we have a few records where we know its III Group J but no unit is known.
    you have it with A's before B's before C's etc as required, but one must only see the Group shown if no number is known. so 1 I A wouldnt appear, 1 A would.
    after numbers 1 2 and 3 are shown for A, then any more records for Group I get shown for A, then 4 5 and 6 get shown for A, then any instances of II A are shown, then finally 7 8 9 for A are shown, then any instances of III A are shown, repeats then for B, then C etc.

    Ajax,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.
    Didnt intend to be dismissive, being uneasy/beginner to coding I simply need to see your or anyones use of your suggestion where it would reside.

    Cheers

    DBenz

  8. #8
    Gicu's Avatar
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    840
    How about this one?

    Cheers,
    Vlad
    Attached Files Attached Files

  9. #9
    DBenz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    69
    Hi,
    well almost, A B C D etc are in correct order, but this gives e.g. the group II for the numbers 4 5 6 before the numbers

    so we have:-
    II A
    II A
    4 A
    5 A
    5 A
    6 A
    III A
    7 A
    7 A
    8 A
    etc

    when we need e.g the group II for numbers 4 5 and 6 after the numbers 4 5 6. and group III which is the group for 7 8 9 to come after the numbers 7 8 9.
    4 A
    5 A
    5 A
    6 A
    II A
    II A
    7 A
    7 A
    8 A
    III A
    III A

    I have added a lot more records to the attached database so the current sequence can better be seen and hopefully the correct sequence as well..

    DBenz

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