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.
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
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?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
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 itselfhow would I do that without using attachments in the table?
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)
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
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
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
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.
Agree with Micron except it should be
“\subfoldername\”
Needs the end backslash
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.
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
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.
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
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.tblSyncPairs lists all the possible Trainer/Pokemon combinations
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.