Rocky Smolin
rockysmolin at bchacc.com
Thu Sep 11 09:17:29 CDT 2014
For table structure I still think the standard header/detail approach is the best. The way data is structured in the tables should, I believe, be done without regard to the way the data is displayed. If the table structure is correct, then you can always format the data to their liking. For the form, if they really want the form to follow the current paper form (not a good restriction - there are so many ways to present data - but if they want it, they want it) I'd make the main form with fields in the area they call "Sample Detail" bound to the header fields. The nomenclature a bit confusing here because the header table fields come from what they call 'detail' at the top of the form. So let's put the detail fields into what we'll call the 'Parent' table and the details about each sample (A, B, C, etc) into the 'Child' table. And then four sub-forms - Stereo Microscopy, the horizontal section, Trace Analysis, and Fibres present. But all four sub-forms would draw from the same child table which would have a FK pointing that the parent, of course, and a field with the letter A, B, C, D, etc. So the only problematical part of the data entry form, and presumably, the report is the horizontal display. Can they give you an upper limit of the number of samples? Without that, it's still possible but at that point, I try to convince them to go vertical. But assuming there's an upper limit, you could try an approach I had to use in a case like this - the sub-form could have all of the columns required but at zero width but with their left property at the right spacing. When a record is accessed, the width of the number of columns matching the number of child records for that parent is set to whatever width you need, plus one more for the new record. The sub-form has a horizontal slider in case the number of child samples it too big for the width of the display. And, of course, the horizontal sub-form needs to be unbound, meaning you load the records for that parent record in the current event of the parent form and write them back to the child table before moving to another record. The advantage here is that it's a lot of fun to write, and you get a chance to be really clever. The downside is that it costs your client a small fortune versus a vertical, continuous form approach. The report is more problematical assuming that the maximum number of child records exceeds the page width. But if money is no object... :) HTH Rocky -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of ACTEBS Sent: Thursday, September 11, 2014 2:14 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Table Structure Ideas Hi Rocky, Just found out that there could be more, which makes it a hell of a lot more complicated. Thanks Vlado -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: Wednesday, 10 September 2014 11:53 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Table Structure Ideas Vlado: Is the number of samples always 4 - A, B, C, D. Or could there be three or six, or even more? Rocky -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of ACTEBS Sent: Tuesday, September 09, 2014 11:27 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Table Structure Ideas Hi Darryl, Yes, sorry I should have made that clearer. I'm basically after ideas for the table structure. Forget about the user interface, I can manipulate it to suit. I just can't come up with an elegant solution for the tables. Thanks Vlado -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins Sent: Wednesday, 10 September 2014 4:21 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Table Structure Ideas Hi Vlado, By the "Want the DB to mirror the form" requirement, I assume you mean for the data entry requirement. How you set up the underlying table structure should be very different. Is that your understanding as well? Cheers Darryl -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of ACTEBS Sent: Wednesday, 10 September 2014 4:11 PM To: access group Subject: [AccessD] Table Structure Ideas Hi Everyone, Got a complex form that we're turning into a DB and having difficulty coming up with a good table structure to suit the form and the way the data is collected and input. They want the Access DB to mirror the form. You can see the form here: http://download.actebs.com.au/FormDoc.jpg As you can see by the example image above the sample are marked as A, B, C, and D, but sometimes they display it vertically and other horizontally, which is confusing the hell out of me. Any idea on how best to design the table structure so the data is easy to work with down the track? Any ideas most welcome. Thanks in advance. Vlado -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com