Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 32
  1. #16
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    This is what I ended up with... I could technically break it out a little further, but I think this is going to be easier to work with... all the data entry points that could be expected to change are 1-spot entry
    Click image for larger version. 

Name:	Capture.jpg 
Views:	26 
Size:	71.5 KB 
ID:	45747

  2. #17
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I probably would not be the only one who will tell you to not use reserved words for object names (e.g. type, class, description) and to not use multi value fields (Pokepic).
    http://www.allenbrowne.com/AppIssueBadWord.html

    http://www.mendipdatasystems.co.uk/m...lds/4594468763
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #18
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Every Link between Tables should be set for Referential Integrity.

    If you do not do this then your current setup would allow you to enter information in a Related Table with No Parent Record.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #19
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    Quote Originally Posted by Micron View Post
    I probably would not be the only one who will tell you to not use reserved words for object names (e.g. type, class, description) and to not use multi value fields (Pokepic).
    http://www.allenbrowne.com/AppIssueBadWord.html

    http://www.mendipdatasystems.co.uk/m...lds/4594468763
    I can fix the column names no problem, but I need to store an associated *.png file for each trainer/pokemon so the image pops up in a subform when a filtered list box is clicked on... how would I do that without using attachments in the table?

  5. #20
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    how would I do that without using attachments in the table?
    by storing them in a common file folder (could be the same as where the BE is or a subfolder off it) and then storing the path to the image rather than the image itself

    other comments - your fields B1L1, 2,3, 4 and B2L1, 2,3, 4 look like denormalised data, would expect to see those in a separate table(s)

  6. #21
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    can you give an example of a filepath field save so I can see the syntax? I'm hoping I can to do this as a local path rather than a full path with drive included, etc for distribution purposes... also what object type is used in this instance? Then the next question is invariably going to be how to display it using VBA callouts in the image box

    You aren't wrong about the B1L1/2/3/4 fields.... I can branch those out

  7. #22
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to clarify what you are actually trying to do - a filepath is a filepath

    c:\docs\images\filename.png

    or perhaps for a server location that everyone can use

    \\myserver\images\filename.png

    if you have a fixed location such as \\myserver\images\ then you don't need to store that part, just have as a constant and store the filename. Then concat the two when you need to get it

    const imagepath as string="\\myserver\images"

    myImagecontrol.picture=imagepath & me.filename

    where filename is the name of the field you have stored the name of the file

  8. #23
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    so I can't just reference the subfolder within the current folder?

    reason being.... if I distribute said database and other users download it, they may have a different drive name/storage pretext but it will always be the named subfolder once you hit the actual stored folder

  9. #24
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you make the image folder a subfolder of the db folder and the image folder maintains the same name for all users (because they know better than to rename it) then all you need is Application.CurrentProject.Path & "\subfolderName" & imageName.jpg
    Then no matter what the path to the db is, that will always be the first part.
    Last edited by Micron; 07-17-2021 at 09:30 AM. Reason: clarification & code correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #25
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Agree with Micron except it should be

    “\subfoldername\”

    Needs the end backslash

  11. #26
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're always on top of things! Thanks for correcting my oversight.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #27
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    Quote Originally Posted by Ajax View Post
    Agree with Micron except it should be

    “\subfoldername\”

    Needs the end backslash
    Thank you for the clarification.... I'm updating as we speak and I'll repost the actual file if anybody wants to look over it before I start all the queries/forms

    Also thank you to Micron.... the attachment thing did strike me as clunky, but the other options I looked up seemed also clunky... pretty happy with this method assuming it works on the output and it dropped 20 MB off the database size removing the attachments

    One question while I'm fixing this...

    tblDamageTypes and tblSyncWeak are the same table duplicated.... it's like that because I had tblDamageTypes > DamageTypeID set as one table and linked to both tblMoves > DamageType and tblSyncPairs > Weakness... it screwed up the proof query pretty royally... not that those tables will ever need updating or change really, and if they did it wouldn't be that big of a deal.... but for the purposes of data normalization.... I'm guessing that was a SQL join issue? I just quickly made the query in design view

  13. #28
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't get the latest question. Why would 2 tables with names that seem to relate to different things be duplicated? Don't know if you got an answer or figured out re: post 7 but your naming convention is likely a contributing factor in confusing yourself and those who try to follow. Imagine how hard that is for us if you can't follow it! I suggest you change the way you name fields; e.g. in tblSyncPairs SyncID (which you have) but where the foreign key is used, SyncIDfk (or SyncID_fk) or Sync > SyncFk. Having differing names in a join probably means you'll need the relationships in front of you when attempting to troubleshoot. Your original relationships pic also shows junction tables with just 2 foreign fields and that makes no sense to me. A junction should involve 2 fk fields which together, create records having other fields of data that are related to combinations of those foreign keys. Those records construct the many to many side of the relationship. If that seems strange, go back to normalization sites and review and concentrate on the use of junction fields.
    HTH.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #29
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    The junction tables were the only solution I could come up with to get rid of the multiple data entry points....

    e.g.
    tblSyncPairs lists all the possible Trainer/Pokemon combinations with a Sync ID (and some other directly relevant data)
    tblMoves lists all the possible moves in the game with a MoveID (Each SyncID will have 5 total moves, some combination of 3 pokemon or item moves, 1 trainer move, and 1 sync move)
    those are the primary keys....

    ... So I made a junction table between them with no primary keys that lists the SyncID 5 times with the corresponding MoveIDs...

    Same thing for the other junction tables, though for Themes and Passives they vary on the amount they have


    As for the Weakness/DamageType thing... there are damage types that each Pokemon deals through pokemon moves, each Pokemon is also weak to one damage type...

    Make more sense now or did I do something incorrectly?

    Updated Relationship grid.... haven't broken out the bonus table yet
    Click image for larger version. 

Name:	Capture.JPG 
Views:	14 
Size:	78.4 KB 
ID:	45777

  15. #30
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    tblSyncPairs lists all the possible Trainer/Pokemon combinations
    Then to me, it seems that tblTrainer and tblPokemon would be pk tables and tblSyncPairs would be the junction? I'm not into gaming at all so there is a lack of understanding the schema on my part. Suffice to say that you have to decide which is the Entity (table) and which are the attributes (fields). When that is a one to many thing (one PO to many PO line items) that is easier. When it is a many to many thing, that's when you need junction tables (many students can take one class and one student can take many classes). Students and classes/courses are separate entities. One to one tables is usually a sign of incorrect design (not that I'm seeing any in your schema at the moment). However, methinks you've got fk identifiers backwards - e.g. MoveClassID is a pk field so it shouldn't have the fk suffix.

    An example of student/course junction

    tblStudent
    StudenID Fname Lname etc
    1 Joe Green
    2 Mary Smith
    3 Sam Brown
    tblCourses
    CourseID Course etc
    1 math
    2 biology
    3 geograph
    tblStudentCourse
    StudentCourseID StudentFk CourseFk
    1 1 1
    2 1 3
    3 2 1
    4 2 2
    5 2 3
    6 3 2
    7 3 3

    There are "many" entries of the same student PK and many entries of the same course PK (as Foreign Key values). Note that the fk fields are not part of a relationship, so you have the ID field and the pk field (as the fk field) from each related table as a minimum. Your schema has the ID field from the related table plus only one field as part of the records that are supposed to be creating a junction. In short, the design seems to have no junction at all if you compare yours to the example above. Whatever issue you had before was likely due to design issues, but I don't know that you want to 'fix' that with another design issue. You might not totally understand normalization wrt junctions, and that's not a slam. If it all sank in immediately and you could design with no errors off the hop, you would be a unique db designer for sure.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 12-23-2018, 05:02 PM
  2. Replies: 16
    Last Post: 01-29-2018, 03:12 PM
  3. Organizing my data in table - normalization?
    By adame in forum Tutorials
    Replies: 8
    Last Post: 06-15-2017, 02:57 PM
  4. Normalization for large amount of data
    By kvollene in forum Database Design
    Replies: 8
    Last Post: 07-01-2016, 01:18 PM
  5. Data normalization, ie; duplicate records
    By snowboarder234 in forum Database Design
    Replies: 1
    Last Post: 08-05-2013, 04:50 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