Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-17-2017, 05:51 AM   #16
plog
AWF VIP
 
Join Date: May 2011
Posts: 7,966
Thanks: 10
Thanked 1,923 Times in 1,884 Posts
plog has a spectacular aura about plog has a spectacular aura about plog has a spectacular aura about
Re: Rad Second Access Project to CRUSH!

Disagree.

Forms that allow data changes (add/edit/delete) should be based on tables, not queries. 1 form, 1 table. This allows you to know exactly where the data on that form is going and always ensures the form can manipulate the underlying data.

To represent a 1-many relationship in forms you would have a main form based on the 1 table and a subform based on the many table.

plog is offline   Reply With Quote
Old 08-17-2017, 06:11 AM   #17
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,635
Thanks: 31
Thanked 1,693 Times in 1,645 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Rad Second Access Project to CRUSH!

tgg1,

You have been given advice/suggestions to your questions-- what is status?

@lightwave
Nice analogy for the form as a window into/onto the database!!
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 08-17-2017, 06:33 AM   #18
Lightwave
Ad astra
 
Lightwave's Avatar
 
Join Date: Sep 2004
Location: Edinburgh
Posts: 1,281
Thanks: 89
Thanked 102 Times in 97 Posts
Lightwave will become famous soon enough
Re: Rad Second Access Project to CRUSH!

Quote:
Originally Posted by plog View Post
Disagree.

Forms that allow data changes (add/edit/delete) should be based on tables, not queries. 1 form, 1 table. This allows you to know exactly where the data on that form is going and always ensures the form can manipulate the underlying data.
OK I should qualify that - there is a body of thought that says that forms should be based on queries but certainly delete and add are better done by queries based on tables.

The larger your datasets the increasingly likely your forms may have to be based on queries.


Last edited by Lightwave; 08-17-2017 at 01:23 PM.
Lightwave is offline   Reply With Quote
Old 08-17-2017, 08:16 AM   #19
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,485
Thanks: 54
Thanked 351 Times in 317 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Rad Second Access Project to CRUSH!

And sometimes you'll need to use queries on edit forms anyway. In my case, I deal with tables with millions (and in one case, billions) of records. Because of that, I generally use a query based on one single table to return a single record based on selected criteria. However, because the query is NOT one of the special non-updatable types and is based on a single table, the data can be edited.

Those types of games aren't as necessary with more reasonable table sizes, however.
__________________
"I do not feel obliged to believe that the same God who has endowed us with sense, reason, and intellect has intended us to forgo their use and by some other means to give us knowledge which we can attain by them." -Galileo Galilei
Frothingslosh is offline   Reply With Quote
Old 08-17-2017, 01:57 PM   #20
Lightwave
Ad astra
 
Lightwave's Avatar
 
Join Date: Sep 2004
Location: Edinburgh
Posts: 1,281
Thanks: 89
Thanked 102 Times in 97 Posts
Lightwave will become famous soon enough
Re: Rad Second Access Project to CRUSH!

Quote:
Originally Posted by plog View Post
Disagree.

Forms that allow data changes (add/edit/delete) should be based on tables, not queries. 1 form, 1 table. This allows you to know exactly where the data on that form is going and always ensures the form can manipulate the underlying data.
Granted I was thinking about use cases such as bookings where a teacher may want to see only those students in a form that are in his classes and do some simple edits to their details.
Lightwave is offline   Reply With Quote
Old 08-17-2017, 02:36 PM   #21
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 1,631
Thanks: 29
Thanked 365 Times in 338 Posts
ridders will become famous soon enough ridders will become famous soon enough
Re: Rad Second Access Project to CRUSH!

Quote:
Originally Posted by plog View Post
Disagree.

Forms that allow data changes (add/edit/delete) should be based on tables, not queries. 1 form, 1 table. This allows you to know exactly where the data on that form is going and always ensures the form can manipulate the underlying data
Sorry but I disagree with the above.
Whilst I can totally understand why Plog is saying that, I regularly work with forms used for editing data that are based on several tables.
As long as they are designed well, it isn't a problem.

Clearly you need to make sure the query isn't read only!
The more tables there are, the more likely it becomes read only.
But if it is read only, by definition you can't edit the underlying tables anyway

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Project Access Leeroybrown Tables 25 06-26-2011 02:32 AM
Access 2003: Create Project and add Records to Project spudracer Queries 7 01-20-2011 01:17 PM
Access 2003 Project (ADP) vs Access 2007 Project ions General 32 03-16-2010 12:00 PM
Converting Access to Access Project Query problem mjliscio Queries 7 09-19-2007 08:38 AM
Deploying Access 2003 project in different Access versions Ayat General 2 12-29-2005 07:21 AM




All times are GMT -8. The time now is 08:43 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World