Results 1 to 8 of 8
  1. #1
    Stevie is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Location
    Salisbury, Adelaide
    Posts
    7

    Question add field after grouping data

    I have 2 tables for Drain Pit data. 1 has the location details. 2 has the Pit details such as Type, Length Width. The tables are related by Pit Type. There are many of the same type of pit with different lengths and widths. I have managed to create a table showing the quantity of each Pit Type with differing sizes but I need to add a field that will automatically assign a letter a,b,c, etc to indicate a Pit subtype. Below I have added the letters manually to show what I want Access to do for me. P.S. I am a newbie to Access.
    i.e.
    NumberOfDups PIT TYPE LENGTH WIDTH SubType
    1 SEP (H) 1000 900 a
    1 SEP (H) 1000 1000 b
    4 SEP (H) 1000 1200 c
    6 SEP (H) 1000 1400 d
    6 SEP (H) 1000 1500 e
    2 SEP (H) 1000 2500 f


  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not seeing a need for 2 pit tables. Why can't all pit characteristics be in the same table? Pit type values would be the (autonumber) PK field of tblPitTypes. A query with tblPits and tblPitType would return Description field data (descriptive text) from tblPitTypes. I think more info is required to suggest how to relate sub types to types but likely the same approach would be the way to go. Do you have type and sub type tables?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    Stevie is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Location
    Salisbury, Adelaide
    Posts
    7
    A Pit Type; in this case a Side Entry Pit SEP can have a length of 1000mm and a width of 900mm (subtype a) another SEP can be L 1000mm x W 1000mm (type b) and so on. There are also other Pit Types. i.e. Grated Pit - GP with subtypes of differing lengths and widths.

  5. #5
    Stevie is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Location
    Salisbury, Adelaide
    Posts
    7
    The reason I have 2 tables is that every pit has a Unique identifier with unique location details but the same type of pit may be in in multiple locations.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Stevie View Post
    The reason I have 2 tables is that every pit has a Unique identifier with unique location details but the same type of pit may be in in multiple locations.
    I still say that's not quite right. tblPit should handle everything about the pit(s). Location table - maybe but probably not (only if there location changes and you want to maintain history, but I doubt that's the case). Pit type seems like it is an attribute of the pit and the fact that the same type could be anywhere really has no bearing on the number of tables for pit data. Somehow I don't think I'll convince you so I'll drop it now.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Stevie is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Location
    Salisbury, Adelaide
    Posts
    7
    Actually it is likely that as-built location could differ from the initial designed location, in which case we will need to record and calculate the difference to check if it is within allowable tolerances. So then we produce a conformance table. Maybe this is all above my pa

  8. #8
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    I do not understand why you want to assign the subtypes afterwards. The subtype could (and should?) be a part of of the subtypes table.
    Reading all the posts I think you could use a model like this:
    Click image for larger version. 

Name:	PIT01.jpg 
Views:	14 
Size:	16.7 KB 
ID:	49215

    For better understanding of what you are doing, please also post your datamodel.
    Groeten,

    Peter

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

Similar Threads

  1. Replies: 1
    Last Post: 05-16-2018, 07:31 PM
  2. Replies: 3
    Last Post: 07-24-2016, 12:28 PM
  3. Grouping Data Into Sections from A Field?
    By MarkF2004 in forum Access
    Replies: 2
    Last Post: 10-10-2014, 09:43 AM
  4. Replies: 1
    Last Post: 04-16-2013, 09:51 PM
  5. Grouping of data
    By tkandy in forum Queries
    Replies: 7
    Last Post: 05-09-2011, 06:07 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