Results 1 to 12 of 12
  1. #1
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99

    Conditional Formatting Rules

    So I've built this db. to track persons and mainly their dates e.g. dates in, dates out, Courtdates, Applicationdate, baildates and the list keeps going in my mind; different TYPES of dates would have many fields in the table. to get around this I've tried a drop down list of types to case numbers affixed to Five [5] dates e.g. date_1 date_2 date_3 all up till date_5 making a personID on one record set i.e. row. but am encountering a slight anomaly; where person(s) just used one or two dates according to their case assessment making other fields in their record empty???. using the many to many relationship one person could have many dates and any given date type could have many persons.



    Question, is this a good critical way of building this db.??, because IF I use the fields as dateTypes i.e. columns and not i.e. row or recordset we are looking at let say Courtdate many entries on that field maybe 1,000,000 entries and counting will access support this amount continuous and I will not have to split the db.

    Question2, under formatting is there a way to color code the [date-text] not access conventional conditional formatting rules e.g. like in Excel any textbox the user could color code the text or field so I'm basically asking under dates_1 through _5 fields could I give the dates a color to mean type of dates affixed to dropdown list e.g. [SGIN 00011] their are like 8 types of this in a dropdown list with the many to many concept... HOPE u guys understand what am asking!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I only have a minute so hopefully someone jumps in with more detail, but your design is not what we call normalized (search on normalization). Normally there would be a related table with the key field from this table plus fields for the date type and date. If I had two dates, I'd have two records in this table. That lets you have 1 date or 1,000, and no design changes necessary if you get a new date type.

    Off to dinner!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Doesn't matter if data is in columns or records, it's still data and size should be about same either way.

    Normalized should be easier to manage, do searches, and as Paul indicated, more flexible.

    I have known people to claim having million records in Access and function just fine.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by June7 View Post
    Doesn't matter if data is in columns or records, it's still data and size should be about same either way.

    Normalized should be easier to manage, do searches, and as Paul indicated, more flexible.

    I have known people to claim having million records in Access and function just fine.
    Note want color to dates to represent meaning say like date_1 blue means person was not present date_2 Green means date person applies High Court Judge, date_3 Purple applies Circuit Judge etc. you'll understand?

    you guys tell me is this not a normalized db.
    Attached Files Attached Files

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    tblCaseAjourn is not strictly normalized. Multiple similar name fields with same type of data is an indicator. As you already recognized, some of the date fields can be left unused. Suppose a need for another date category arose?

    It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."

    Wouldn't "ajourn" be properly spelled as "adjourn"?

    Yes, dates can be color coded based on value in another field. Why not use Conditional Formatting?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    yep lol didn't observe that spell error.... Again this is jus a pseudo copy. Original tblCaseAdjourn is linked to tblCaseMatters in another project NOTE can i color code the [date-text] fields???
    Quote Originally Posted by June7 View Post
    tblCaseAjourn is not strictly normalized. Multiple similar name fields with same type of data is an indicator. As you already recognized, some of the date fields can be left unused. Suppose a need for another date category arose?

    It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."

    Wouldn't "ajourn" be properly spelled as "adjourn"?

    Yes, dates can be color coded based on value in another field. Why not use Conditional Formatting?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Can color code in a form or report.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Click image for larger version. 

Name:	Structure1.png 
Views:	28 
Size:	101.2 KB 
ID:	41835
    Should Not link One-to-One


    I use a suffix of "_PK" for the Primary Key field and "_FK" for the linking Foreign Key field. Easier to determine what field is what PK or FK in queries. (for me)
    Here is how I would link the address table
    Click image for larger version. 

Name:	Structure2.png 
Views:	28 
Size:	109.9 KB 
ID:	41836



    My 2cents.....

    Good luck with your project.

  9. #9
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    ssanfu thanks for your tip, but believe me I've learn a lot in the couple of months i have join this forum their many intelligent people in this world. i understand the suffix ID_PK primary key and ID_FK foreign key to adopt this concept in relational db i have internalize my own concept for my visuals base on each table e.g. the main table primary key using name ID every other supporting table or dependent table base on each table suffix will be different in the name foreign key but this is for the developer of the db e.g. name_ID[primary] another table name_AJ [foreign] each table will be easier to track especially where programming is involved..... PLEASE NOTE this is not the real db am working on its a template

    1 in the db there are many to many relationship e.g. tbloff jun one person many offences, one offence many persons in our system there will be many to many relationships like this
    2 tblAddress was thinking composite keys note in another db its two keys in that tbl because we want one person to be uniquely identified to that tbl; once you are tied to that info you don't show up again even same name persons hence address_1 and _2 i.e. persons with two Address
    3 the question of color [text-date] field as in excel not applying formatting rules because again in our system you can have a date and it could mean many things...;guess I'll have to use memo fields

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PLEASE NOTE this is not the real db am working on its a template
    This is not helpful to get relevant answers.....

    1) many-to-many relationships are good. I use them a lot.
    2) I never use composite keys - instead I use composite INDEXES. tblAddress is linked to tblImnates - one inmate can have many addresses and one address can have one inmate. If you want fields Address_1 and Address_2 to be unique, then you set a compound INDEX..

    3) not sure I understand. Maybe examples of colors and dates would help. In EXCEL, did you manually set the colors?

  11. #11
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by ssanfu View Post
    This is not helpful to get relevant answers.....
    I understand. its a real db. where there're sensitive info so am thinking u notice am tracking offenders in the county jail as I shared; In this environment there will be MANY.... many to many relationships.

    Quote Originally Posted by ssanfu View Post
    2) I never use composite keys - instead I use composite INDEXES. tblAddress is linked to tblImnates - one inmate can have many addresses and one address can have one inmate. If you want fields Address_1 and Address_2 to be unique, then you set a compound INDEX..
    So am internalizing I have this MAIN table all relationships is connected here tblInmates but this table could have upwards of say 50 fields to get around this I tried breaking the MAIN table into 2 or 3... etc. so far so good lol tblAddress and tblDescription want to connect these tables uniquely with no duplicates even with persons of same name???? Hope you're following me ssanfu.... lol trying to type and make sense.


    Quote Originally Posted by ssanfu View Post
    not sure I understand. Maybe examples of colors and dates would help. In EXCEL, did you manually set the colors?
    RIGHT ... so these guys have dates to see a Judge; Many dates; but it could mean many things I've create a dropdown list field in tblCase Adjournment to try and capture this However I still encountered something e.g. persons NOT present; could create a YES/NO fields through dates_1 to _5 but not applicable another anomaly.... u have several dates but all for different matters before different Judges want to color code this because two persons can go to the same Judge applying for different things

    Quote "u have several dates but all for different matters before different Judges want to color code" so on 2/11/19 you were not Present I want to put this in red indicating this date u were not Present to see a Judge not using Conditional formatting rules which says...."not equal to, greater than, less than" etc. because on the other hand 2/11/19 another guy was present....Understand????
    WOW u guys really have me learning not jus talk tech but to write tech as well.... tried my best to explain.... THANKS

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What about this:
    Create a COPY of the dB.
    Using the COPY, rename it to something with the word "COPY" so it doesn't get confused with the real dB.

    Delete the sensitive data. It is OK to leave static data - for example leave the offenses: "Jaywalking", "Littering", "Speeding", "Spitting on Sidewalk", etc.

    Put in dummy inmate names/info: Mickey Mouse, Donald Duck, Goofy, Dick Tracy, Fred Flintstone, Bam Bam......
    Then post it dB (copy) here.

    "u have several dates but all for different matters before different Judges want to color code" so on 2/11/19 you were not Present
    So there must be some method in a record to indicate that an inmate missed the meeting with the judge? You can create a CF rule that if a field is equal to "Not present", then set the meeting date (or whatever the field name is) to Bold/Red. That rule would be for the date field based on the status field.
    Attached Files Attached Files

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

Similar Threads

  1. Conditional Formatting Rules in Access 2010
    By adillpickle in forum Forms
    Replies: 6
    Last Post: 04-01-2016, 07:51 AM
  2. Replies: 18
    Last Post: 09-24-2014, 02:44 PM
  3. Replies: 16
    Last Post: 12-02-2013, 06:20 AM
  4. Replies: 6
    Last Post: 05-26-2013, 09:10 PM
  5. Replies: 2
    Last Post: 01-21-2013, 10:38 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