Results 1 to 4 of 4
  1. #1
    kywilson25 is offline Novice
    Windows XP Access 2013 32bit
    Join Date
    Feb 2017
    Posts
    2

    Sort across Multiple Fields DCount help

    I have [tbl1] that is one line per [ID] with fields [Jur1] to [Jur9] (Short Text) which are related to fields [Per1] to [Per9] (Number).
    ID Jur1 Jur9 Per1 Per9
    1 00 99 1 0.8
    2 99 00 0.5 0.8
    3 80 S5 1 1
    4 A5 00 0.8 1

    I made [tbl2] that has Field [JurT] with records 1-9 to make a [que1] with IDs listed 9 times and sorted by [ID] then [JUR].
    ID JurT Jur Per
    1 1 00 1
    1 9 99 0.8
    2 9 00 0.8
    2 1 99 0.5
    3 1 80 1
    3 9 S5 1
    4 9 00 1
    4 1 A5 0.8

    Then I have [tbl3] relates [UID] to [ID]

    UID ID
    1 1
    1 2
    2 3
    2 4
    Then make [que2] groups by [UID] [Jur] and sum [per]. Sort by [UID] then [JUR]. ?????How do I number [Jur] 1-9 per [UID] to get back to original format [JurT2]??????
    UID Jur Per JurT2


    1 00 2 1
    1 99 1.3 2
    2 00 1 1
    2 80 1 2
    2 A5 0.8 3
    2 S5 1 4
    To get back to this format using iif statments in Jur and PER.
    UID JUR1 JUR2 JUR3 JUR4 JUR9 PER1 PER2 PER3 PER4 PER9
    1 00 99 2 1.3 0 0 0 0
    2 00 80 A5 S5 1 1 0.8 1 0 0

    I have tryed to use DCount but I get lost in the is ' " [ "*" jargon. Please explain why " or [ are used.
    Thanks

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You enclose an Object Name that has an embedded space with [Embedded Space]
    The single and double quote are delimiters for TEXT or String values.
    You may wish to Google Normalization as you have some issues.
    Properly normalized, most reports flow very easily.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to Allan's comment, I think you have a basic design flaw. The Per 1--Per 9 is a red flag for a lack of Normalization and poor relational design (as Allan noted).

    Here is a link to a related post in which the poster was learning Access. Post #7 has a lot of fundamental reference info on relational database.

    Good luck.

  4. #4
    kywilson25 is offline Novice
    Windows XP Access 2013 32bit
    Join Date
    Feb 2017
    Posts
    2
    Do you know how generate the field [JurT2]. I have tried to use DCount fn but it does not work.
    Thanks

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

Similar Threads

  1. DCount multi value fields
    By Xterra14s in forum Access
    Replies: 6
    Last Post: 08-10-2016, 09:06 AM
  2. Replies: 2
    Last Post: 10-03-2014, 11:57 PM
  3. Sort Multiple Fields in a Split form
    By NateL in forum Access
    Replies: 3
    Last Post: 05-01-2014, 01:38 PM
  4. Replies: 3
    Last Post: 01-08-2013, 02:41 PM
  5. Replies: 1
    Last Post: 03-12-2012, 02:41 AM

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