Link to query or table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with tab pages/subforms based on queries.

Should I base the subforms on queries or directly on the main table when the
fields in the queries come from the main table?

If the answer is the query, what field should I link to—the table ID or one
of the fields in the query?

Thank you

Peter
 
Peter

From your description, your subforms are based on the same data as your main
form (" ... or directly on the main table ..."). Subforms work great when
there's a one-to-many relationship between main form data and subform data
(i.e., two tables, not one).

Can you provide a bit more explanation and perhaps an example?

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Jeff
If you wouldn't mind looking 7 questions below this one you will see my
previous question:

40 separate forms or 1 form with hidden tabs?

This summarizes what I have done and explains why I am basing the subforms
on the main table.

I'm building a travel site. The records will contain reviews of Dining,
Accommodation, Museums, etc. Each record will be attached to a destination
(in a linked table).

There are 9 subforms each on a separate tab page.

As an example: One tab page/subform tracks the writer's progress with fields
such as Progress, PublicationStatus, LastUpdated, NextUpdateDue, etc.

Should the 9 subforms each be based on a query or directly on the table? If
the answer is query, what fields should I link?

I'm a novice. My forms do what I want, but before I build on what I've done
so far, I am reviewing my design decisions with the help of experts such as
yourself.

Thanks

Peter
 
Better yet here's the Q & A

Question:

I have a database which will have about forty forms.

There are nine tab pages that common to every form and are based on the same
linked tables.

The controls in each tab page are in a subform, so that if I need to make a
change, I can make it to one subform rather than forty forms.

Each form will also have one or two unique tab pages/subforms are based on
separate tables.

Is this the best way to do this? Or should I not use subforms and instead
put all the tab pages on one form and hide the tab pages that aren’t needed
when the user selects the form they want?

Answer:
A Form with 40 "live" (even if hidden) subforms will probably be ungainly
and VERRRY slow to open (all subforms must be opened and populated first
before the form is displayed).

I'd suggest keeping the form "light" if possible and using techniques such
as dynamically changing the Recordsource and/or SourceObject properties of
the subforms.

John W. Vinson[MVP]
 
Addenda: despite what I said in my question I understand I will have to link
child/master fields whichever way I go.
 
Peter

I checked the Q & A you referenced. I'm not sure I saw the same
explanation/description there as you've given here.

Perhaps if you described further the structure of the data/table you are
building so many subforms against...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Good Evening Jeff. Thanks for the interest in my question.

The database is for an online travel guide (will migrate to MySQL).

Here's a description of my tables and forms

tblDestinationsGlobal: is one-to-many to
tblLocalDestinations: which is one-to-many to
tblMain (the ID = MainID)

tblMain is the record source for frmText.

This form has a tab control with 9 pages.

On each page of the tab control there is a subform based on queries.

Except for the first page/subform, each query is solely based on tblMain.

EXamples of what's on the 9 pages of subforms:
A list box that is used as a record selector etc.
An OLE object (Word document) for entering text etc.
An OLE object for attaching maps (plus the path to the map) etc.
An Option group for recording the writer's progress, Publication date,
Update, etc.

There will be approx. forty separate tables attached one-to-one to tblMain

e.g.,
tblDining
tblAccomodation
tblRetailer
etc.

I have created 40 subforms (with additional items such as Price, Opening
Hours, etc.) with these forty tables as record sources.

I will create forty identical forms from frmText. To each of these I will
add a unique additional page(s) based on one of the subforms.

All of this enables me to later make an adjustment to one subform and the
changes will appear in each of the 40 forms.

I hope that the preceding explanation is clear, it took me quite a while to
think it through (the explanation AND the design).

I've included MainID in each of the queries for 8 of my subforms and used
MainID for my Master/Child.

My question is: Should I continue to base my subforms on queries or attach
them directly to tblMain?

Thanks

Peter
 
Peter

My comments in-line below...


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Peter Stone said:
Good Evening Jeff. Thanks for the interest in my question.

The database is for an online travel guide (will migrate to MySQL).

Here's a description of my tables and forms

tblDestinationsGlobal: is one-to-many to
tblLocalDestinations: which is one-to-many to
tblMain (the ID = MainID)
Click to expand...

I still don't have a sense of what data elements are being kept in these
tables (the titles of the first two allude to possible data). I'm having
trouble imagining how Global Destinations can be related at all to Local
Destinations ... I'd have thought that there would be destinations, some of
which could be categorized as local (say, less than 100 miles) and global
(more than 100 miles).

I have no idea what tblMain contains.

Could you describe this further?
tblMain is the record source for frmText.

This form has a tab control with 9 pages.

On each page of the tab control there is a subform based on queries.

Except for the first page/subform, each query is solely based on tblMain.
Click to expand...

What is the first page based on?
EXamples of what's on the 9 pages of subforms:
A list box that is used as a record selector etc.
An OLE object (Word document) for entering text etc.
An OLE object for attaching maps (plus the path to the map) etc.
An Option group for recording the writer's progress, Publication date,
Update, etc.

There will be approx. forty separate tables attached one-to-one to tblMain

e.g.,
tblDining
tblAccomodation
tblRetailer
etc.
Click to expand...

Are you saying that whatever it is you are keeping in tblMain, it has
additional characteristics such as "Dining" and "Accommodation" and
"Retailer" and ... (40 categories of additional characteristics)?

What kinds of data are you keeping in these forty tables?
I have created 40 subforms (with additional items such as Price, Opening
Hours, etc.) with these forty tables as record sources.
Click to expand...

Are those "additional items" data elements on the 40 tables? Are the same
items kept on each of the forty?
I will create forty identical forms from frmText. To each of these I will
add a unique additional page(s) based on one of the subforms.
Click to expand...

As soon as I see "forty identical forms", I have to wonder if a single form
wouldn't do. If you are using one form per "40 tables", maybe the data
could be kept in one table, with a category field added. That would require
only one form, right?
All of this enables me to later make an adjustment to one subform and the
changes will appear in each of the 40 forms.
Click to expand...

I don't understand the circumstance under which you'd need to adjust just
one form, if you have "forty identical forms".
I hope that the preceding explanation is clear, it took me quite a while to
think it through (the explanation AND the design).

I've included MainID in each of the queries for 8 of my subforms and used
MainID for my Master/Child.

My question is: Should I continue to base my subforms on queries or attach
them directly to tblMain?
Click to expand...

As I said earlier, it is rarely necessary (or a good idea) to have subforms
based on the same table as the main form. When this is done, it is
typically done to try to work around a data design that isn't
well-normalized. One of the reasons for spending the time to normalize a
data structure is to be able to easily use the features and functions that
Access offers.

It sounds like you are working around a data design, rather than having a
data design that permits easy use of Access.
 
Good Evening Jeff. Thanks for your continued interest.

The database is normalized. I was an analyst/programmer 30 years ago and
although I designed the database without knowing about normalization, I was
rigorous in my design. I analyzed my projected record fields using Excel and
when I began to put it into Access and found out about normalization
(duh-thank you MVPs), I had by fortunate accident already identified where to
split my tables.

Here's some answers to your questions and hopefully a clearer picture of my
tables.

Q: I still don't have a sense of what data elements are being kept in these
tables (the titles of the first two allude to possible data). I'm having
trouble imagining how Global Destinations can be related at all to Local
Destinations ... I'd have thought that there would be destinations, some of
which could be categorized as local (say, less than 100 miles) and global
(more than 100 miles).

A: Sorry I've lived with this for so long, that I presume too much. I often
explain the things that are obvious and provide minimal information about the
complicated. Maybe this will make it clearer

#1
tblContinents (surprise-I'd forgotten all about this one).

linked one-to-many to:

#2
tblDestinationsGlobal: Global destinations are countries or parts of
countries that are remote from the country they are a part of (such as
Alaska, Hawaii, Aruba, French Polynesia, Hong Kong etc.) or have a separate
identity from the country they are a part of (such as Hong Kong).

linked one-to-many to:

#3
tblDestinationsLocal: Local destinations are regions, subregions, cities,
and towns within the Global destination.

linked one-to-many to:

#4
tblMain: contains text about Restaurants, Hotels, etc. in the Local
destination plus housekeeping details of Progress, Publication date, Update
due, etc. plus links to photos and maps etc. All fields in this table could
be required for any record in #5 below.

each record linked one-to-one to one of 40 tables

#5
E.g.
tblDining: the restaurant's opening hours, cuisine, etc.
tblAccommodation: the hotel's prices, amenities, etc.
tblRetailer: the retailer's opening hours, etc.
tblSite: the museum or gallery's opening hours, entry fees, etc.
approx. 36 more

Q: What is the first page based on?

A: The first page is a conventional subform based on a query from
tblContinents, tblGlobal Destinations, and tblLocalDestinations. This page
shows the path to the local destination. E.g., North America-USA-West
Coast-California-San Francisco-Fisherman's Wharf.

Q: Are you saying that whatever it is you are keeping in tblMain, it has
additional characteristics such as "Dining" and "Accommodation" and
"Retailer" and ... (40 categories of additional characteristics)?

A: The fields that are required for every record are in tblMain (#4). The
additional characteristics (fields that are different) will be in the 40
tables (#5)

Q: I don't understand the circumstance under which you'd need to adjust just
one form, if you have "forty identical forms".

A: I will make 40 copies and then add a unique page(s) to each form whose
record source will one of the forty tables. After I add the extra page(s)
each form will then be slightly different from the others.

Q: It sounds like you are working around a data design, rather than having a
data design that permits easy use of Access.

A: The data design is very important, but #1 for me user satisfaction. The
writers who are going to use the forms think they are very user-friendly with
dialogue boxes that prevent wrong actions and make suggestions. When I asked
for suggested changes-they said they wish had something so useful years ago.
I see people on the Access forums talking about wait times on their forms,
but my prototype jumps from destination to destination and populates the list
box instantly.

Because I got the form design right (I hope-touch wood-cross fingers-we'll
see), I didn't have to put huge amounts of code on the form.

My intention is to do all the data entry on the project for three-or-four
months until the forms are close to perfect as I can get them and then give
them to the writers and ask for suggested modifications.

My Q: Why do I want to do it this way?

A: Say I want to add a button or code to a page in my 40 forms that is based
on tblMain. All I have to do is change the subform and the change appears in
all 40 forms.

Feel free to shoot me down in flames.

Thank you

Peter
 
Peter

On the contrary, it sounds like you have something that is working.
Performance in production and happy users is what it is all about, in the
end.

I'm not sure I can help, though, given my (still) limited understanding of
your data design. But hey?! If it works for you?!!

Best of luck


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Back
Top