Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 36
  1. #16
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101

    Hi Steve, it is #1 the gives me grief, in the original db I could open the other two tables but cannot open tblInspectionReports. The OP mentioned MVF but I thought those were introduced back in 2007 so I'm not sure what other new features he might have used.
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  2. #17
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Vlad, not really following but see if you can open this db. I took a guess as to what the issue might be. I had to remove 2 fields, which the copy of the table still has. If you can open the table in question, it's likely you won't be able to open the copy. If you can't open this one, then I don't know what the issue is as I didn't find any BigInt or data macros and the like, which I thought you should be able to open anyway (I mean the data macros). The db as posted earlier still has multi value fields.
    DB Sample.zip
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #18
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    @micron
    Thank you very much! The copy works so those two fields must have been the problem, were those the MVF fields?
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #19
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hmm.. I really want to learn how to do this.

    Right now there are multi-value fields for up to 8 selected data that is entered in a single field. So, we don't want to create multiple fields (up to 8) in our parent table? How else will each data be linked to that specific record. How will the form look to enter multiple data related to multiple fields?
    Please review my post #9 regarding the new table(s). For each field that you currently have set up as a MVF you need to create two tables,one that stores the PK of the main record and the FK of the other (lookup) table.

    So for the InspectionType field you would need to create a lookup table (tblInspectionType) with an InspectionTypeID primary key (autonumber) and an InspectionType (short text) description fields similar to your current options. You would also need to create a new linking table called tblInspectionReportTypeLinking or similar which would have its own autonumber PK field, the DIRID_FK foreign key for the inspection report record and the InspectionTypeIDFK for the foreign key associated with the inspection type selected.
    Data entry\presentation for this type of relationship is usually done in a form\subform format but sometimes you could use listboxes or other means to present the info (using concatenation code to put them together in a text box, etc.).

    I am attaching the sample that Micron help me with to show how I thought the answer to your original question was (in the relationships window).

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #20
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Gicu View Post
    @micron
    Thank you very much! The copy works so those two fields must have been the problem, were those the MVF fields?
    Cheers,
    Vlad
    They are lookup fields and the data source is a sql statement - something I've never seen before. That's partly because I'd never use lookup fields. Anyway, I took a wild guess that your version could not work with a lookup based on sql. If you could open that table after I removed those 2 fields, then lookup field itself isn't the problem because I left in the lookup field that is a value list (Shift) - another thing I've never seen or done. I also removed the relationships in order to remove those fields.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #21
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Thanks again, I suspected that was the case as they looked like lookup fields in the original screen shot (with the + sign for the related source) but haven't had any issue before with lookup fields (I never use them either along with MVFs, attachments and all the newer "improvements").

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #22
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Vlad,

    Attached is the tblInspectionReport.xlsx; you can import into the database. There isn't too much on there except linking the relationship with QEIID to QEInames; The SpecialInspector1 / 2 should be linked with tblLabnames.
    Attached Files Attached Files

  8. #23
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi,
    Here is an updated file where I removed the Yes\No fields from tblLABnames and replaced them with one linking table and one lookup table. In the future if you need to add a new certification you would simply add a new record in the lookup table instead of adding a new field to the table then having to modify all related queries, forms, reports, etc.
    You can do the same with the InspectionType MVF from tblInspectionReports.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #24
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Vlad,

    I opened the tblLabnames and these are the subgroups tied to DIR. It doesn't seem like DIR records are correlated to LabID. I wonder if trying to tie both LabIDs (SpecialInspector1 and SpecialInspector2) is forcing to query that fits for both criteria for the ISI name

    For example PB's DIR records will only show only if fields for SpecialInspector1/2 has PB and PB?

    Click image for larger version. 

Name:	picture1.JPG 
Views:	28 
Size:	122.4 KB 
ID:	44560

  10. #25
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you please explain what are you trying to get? What you show is the subdatasheet feature of the table itself which I always turn off (set the subdatasheet to (none)). It is not a query, the sample you uploaded has no queries. Where\how do you want to look at PB's records? I have added a subform to the new frmLabNames to show all inspections associated with a name either as SpecialInspector1 or 2.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #26
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here is my version of the dB.

    I modified Vlad's version "DB SampleVlad2.accdb". (Nice)
    I renamed fields, and I created a couple of tables, mostly to get rid of the MVF. And I separated "Name Field" into "First and Last" name fields.
    There are a few more things I would change, but I would want the full dB. It appears that many tables were not included in the OP upload.

    Click image for larger version. 

Name:	Relationship1.png 
Views:	28 
Size:	39.8 KB 
ID:	44564


    DB Sample_ssanfu1.zip

  12. #27
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Wow amazing work; thank you both.

    Is it normal to see these as ID's on the Tables instead of actual "names". If we wanted to put back tblInspectionReports into names instead of IDs, we can just create a query to do so?

    ssanfu, thanks for showing me how to get out of the MVF; it looks like I need to create tblInspections; did you have to create this manually?

    In a normal setting, if my fomrInspectionReports, should I just get rid of MVF and just multiple columns for InspectionType? Or just it the way it is then try to create something similar to tblInspections?

    Thanks again

  13. #28
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You do not want to replace MVFs with multiple columns (like you had in tblLABnames), you want a lookup table holding one record for each inspection type and one combo box on the form that is bound to the inspectiontype field from tblInspectionReports but that has as rowsource the inspection records from the lookup table. And for that also I would recommend you changing the field type to number and store the ID instead of the inspection type description. Storing the IDs rather than text descriptors is more efficient and the preferred way.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #29
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by raychow22 View Post
    Is it normal to see these as ID's on the Tables instead of actual "names". If we wanted to put back tblInspectionReports into names instead of IDs, we can just create a query to do so?
    You can do whatever you want. IMO, replacing the FK field with the Names is the wrong way to go. You shouldn't/don't want to duplicate data. Let's say the PK value 2 is "Robin Child". She gets married and changes her name to "Robin Thompson". Now you have to go through all records (and all tables where names might be) and change her name. If you used a numeric FK, all you have to do is change her name in 1 table.
    In fact, you could change her name to "Banana Pudding" - nothing else has to be changed.


    Quote Originally Posted by raychow22 View Post
    ....ssanfu, thanks for showing me how to get out of the MVF; it looks like I need to create tblInspections; did you have to create this manually?
    You didn't include your table "tblInspectionType", so I had to create my own. I copied the "InspectionTypes" data from the table "tblInspectionReports" into Excel. I ended up with 5 columns, so I moved all to one column. I sorted the column, then deleted the duplicates, which I exported as a text file. I imported the text file into Access.
    Then I created a query (2 c0lumns-> SELECT tblInspectionReports.DIRID, tblInspectionReports.InspectionTypeFROM tblInspectionReports and saved the results in a text file. I wrote a small routine to parse the text file and inserted the data into "tblInspections".


    Quote Originally Posted by raychow22 View Post
    In a normal setting, if my fomrInspectionReports, should I just get rid of MVF and just multiple columns for InspectionType?
    Yes, I would get rid of the MVF.
    No. That would violate the rules of normalization. In some table, you have "Location1", "Location2", "Location3". Repeating fields is a sure sign of violating the rules normalization. What happens if you need to add 2 more locations? You have to modify the table, the queries, the form and the reports. If "Locations" were in their own table (many side), you just have to add the new data.
    Same goes for "InspectionTypes".





    Rules Of Normalization poster.

  15. #30
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Quote Originally Posted by ssanfu View Post
    You can do whatever you want. IMO, replacing the FK field with the Names is the wrong way to go. You shouldn't/don't want to duplicate data. Let's say the PK value 2 is "Robin Child". She gets married and changes her name to "Robin Thompson". Now you have to go through all records (and all tables where names might be) and change her name. If you used a numeric FK, all you have to do is change her name in 1 table.
    In fact, you could change her name to "Banana Pudding" - nothing else has to be changed.



    You didn't include your table "tblInspectionType", so I had to create my own. I copied the "InspectionTypes" data from the table "tblInspectionReports" into Excel. I ended up with 5 columns, so I moved all to one column. I sorted the column, then deleted the duplicates, which I exported as a text file. I imported the text file into Access.
    Then I created a query (2 c0lumns-> SELECT tblInspectionReports.DIRID, tblInspectionReports.InspectionTypeFROM tblInspectionReports and saved the results in a text file. I wrote a small routine to parse the text file and inserted the data into "tblInspections".



    Yes, I would get rid of the MVF.
    No. That would violate the rules of normalization. In some table, you have "Location1", "Location2", "Location3". Repeating fields is a sure sign of violating the rules normalization. What happens if you need to add 2 more locations? You have to modify the table, the queries, the form and the reports. If "Locations" were in their own table (many side), you just have to add the new data.
    Same goes for "InspectionTypes".





    Rules Of Normalization poster.

    Thanks ssanfu for the insight.

    So if I were use tblLocations and tblInspections as the data entry for my tblInspectionForm. The data would go to those locations instead of tblInspectionTable (linking them to the specific DIRID associated for that record)?

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

Similar Threads

  1. Replies: 14
    Last Post: 10-24-2018, 04:37 AM
  2. Replies: 20
    Last Post: 01-09-2017, 12:08 AM
  3. Help allocating Primary/Foreign Keys and Relationships
    By rosscortb in forum Database Design
    Replies: 2
    Last Post: 07-08-2015, 08:12 AM
  4. Replies: 11
    Last Post: 06-11-2012, 12:23 AM
  5. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM

Tags for this Thread

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