Theory Question About Forms and Underlying Queries instead Tables (1 Viewer)

[DBApprentice]

Hello again Access Experts!

I have a theory question that has been boggling my mind for a while and I would like you wisdom to answer it:

Why one should use a query to underly a form instead of the table originating the query?

I have seen a lot of people stating that but I do not see clearly the reason for this, maybe it is a perfomance booster or somehting else?:banghead:

Appreciate your help!
Diogo Cuba

PS: I appologize in advance to the Moderators if I have posted on the wrong forum, initially I consider to put it in Theory & Pratice but then I decided to put here.

 

[jdraw]

It's usually considered a good practice, but I haven't seen anything from M$oft that details why.

I did find this at FMS

Base Forms on Queries-Minimize Fields Returned

Base forms and subforms on queries rather than tables. By doing this, you can use the query to restrict the number of fields returned, making the form load faster.

 

[theDBguy]

One other reason is to apply a filter and/or sorting order to the data you’re displaying.

 

[Micron]

In addition to being faster when filtered, queries are far more flexible:
- why load every field if you don't need them all?
- queries are pretty much the only way to guarantee a sort order
- queries allow for presenting calculated fields (I don't use calculated fields in tables)
- they are the only way to load data from related tables in a single form

If you need one more reason to think about, the link below is old info and may not be applicable anymore, but given the above reasons, personally I'd refrain from basing forms on large tables anyway.
http://allenbrowne.com/bug-02.html

 

[arnelgp]

..also you can create a Query from different tables by linking them.
therefore you can update more than 1 table at a time.

 

[Micron]

@arnelgp: Just in case you're referring to something else, is that different from my last bulleted point regarding queries?

 

[DBApprentice]

It's usually considered a good practice, but I haven't seen anything from M$oft that details why.

I did find this at FMS

jdraw, thanks for the response! I have saved the link on my references.

 

[DBApprentice]

One other reason is to apply a filter and/or sorting order to the data you’re displaying.

In this case, if I have lookup fields at form level, does that sorting will display the results from the query can be controlled, right?

For instance:

PeopleID | FirstName | LastName
1 Zoolander Smith
2 Xander Cage
3 Alvin Squirrel

The query could be sorted by LastName and my Lookup field would display:

1st Xander Cage
2nd Zoolander Smith
3rd Alvin Squirrel

That way I don´t have to set table level lookup fields?

Regards,
Diogo Cuba

 

[DBApprentice]

- why load every field if you don't need them all?

You know what? That´s the part of DB Design that messes me around, because I used to be more of an Excel guy and building small applications didn´t make me think too hard about the DB design... You simply throw all information in sheets and write VBA and Forms to return what is needed.

When it comes to Access it is an entire different thing because you have to give it more thought on Relationships, Normalization, DB Design and you have to go skecth the diagrams and ponderate many aspects like scalation, number of users, front end and back end.

I am now positive that GroverParkGeorge is right when it comes to n00bs:

"IMO, the single biggest problem new Access users face is not knowing how to design and implement a valid, well-normalized, set of tables. If one doesn't get that right, it's tough sledding all the way."

I think I have to study more this subject.

 

[jdraw]

I agree with GPG. Lots of noobs (and many not so noobs) read or hear the M$oft marketing materials and think that Access software will "magically build a perfect database" for them. Not so!

Here is a link with lots of info on Database Planning and Design. The tutorials from RogersAccessLIbrary are great for learning and experiencing design if you work through them. The other materials are great for concepts and reference.
Good luck.

 

[Micron]

does that sorting will display the results from the query can be controlled, right?

Not sure I understand that question or your terminology about lookup fields at form level. For the first part I think you're asking if a combo or listbox list can be sorted by a query. The answer would be yes, regardless of what it's based on. That brings me to the next point. I'd characterize a lookup field as something that's based on (for example) a combo in a table field, which is something I'd avoid. A control based on a lookup table is another matter. I characterize that as a table whose purpose is to provide a list for a combo or listbox. As I mentioned, regardless if that list comes from a lookup table or any other data table, it can be sorted via a query. Hope that helps.

 

[Uncle Gizmo]

>>>"IMO, the single biggest problem new Access users face is not knowing how to design and implement a valid, well-normalized, set of tables. If one doesn't get that right, it's tough sledding all the way."<<<

There's a particular trap Excel developers moving to MS Access fall into, I call it "Excel in Access" and I blogged about it here:-

http://www.niftyaccess.com/excel-in-access/

Basically it's so easy to move Excel data directly into Access you can spend days building what you think is a good database. Then, when you need to extract useful information, you find it's practically impossible, leaving you no option but to go right back to the beginning and start again!

Sent from my SM-G925F using Tapatalk

 

[DBApprentice]

Not sure I understand that question or your terminology about lookup fields at form level. For the first part I think you're asking if a combo or listbox list can be sorted by a query. The answer would be yes, regardless of what it's based on. That brings me to the next point. I'd characterize a lookup field as something that's based on (for example) a combo in a table field, which is something I'd avoid. A control based on a lookup table is another matter. I characterize that as a table whose purpose is to provide a list for a combo or listbox. As I mentioned, regardless if that list comes from a lookup table or any other data table, it can be sorted via a query. Hope that helps.

It is based on the "Evils of Lookup fields" article, I should have a query underlying my form for a couple of reasons but I should not make lookup fields on my table.

For instance, consider tbl1Departments and tblSupervisor, if I need to build a form to enter new supervisor I will have to say to which department he is assigned. I will have to make a form-level lookup field to retrieve that information so that the user can read "HR".

At least that is what I understood it was supposed to be done.

 

[DBApprentice]

>>>"IMO, the single biggest problem new Access users face is not knowing how to design and implement a valid, well-normalized, set of tables. If one doesn't get that right, it's tough sledding all the way."<<<

There's a particular trap Excel developers moving to MS Access fall into, I call it "Excel in Access" and I blogged about it here:-

http://www.niftyaccess.com/excel-in-access/

Basically it's so easy to move Excel data directly into Access you can spend days building what you think is a good database. Then, when you need to extract useful information, you find it's practically impossible, leaving you no option but to go right back to the beginning and start again!

Sent from my SM-G925F using Tapatalk

I found interesting this part:

Many people get bogged down in the relationship issue, working out the relationships early on and imposing them on the system. My advice is don’t; don’t use relationships, only use then when you can see the benefit, and if you do use them use them right at the end when you know how your database fits together. Incorrectly formed relationships cause problems that are difficult to locate and can cause you headaches in the development process. And they’re not necessary; your database will function quite happily without relationships.

Considering your PoV, I should finish my DB and then start checking how the data relates to each other?

 

[Uncle Gizmo]

Considering your PoV, I should finish my DB and then start checking how the data relates to each other?

One problem with setting up relationships is if you get them wrong, then it can cause problems that are hard to track down. If you're absolutely sure of the relationship, you understand it, you've tested it and you know you're getting the results you expect, then by all means establish the relationship.

However if you're still adding tables you haven't decided if a set of data belongs in one table or another, or you are constantly dropping in and out different tables containing different data sets, then relationships can be very annoying.

 

[Uncle Gizmo]

There have been some interesting discussions here on this subject, see this thread:-

https://access-programmers.co.uk/forums/showthread.php?p=1110510#post1110510

BTW - boblarson made some interesting and relevant comments which unfortunately he decided to delete, a shame really because it's better if you can see alternative points of view, as seeing different points of view helps you make up your own mind about how you want to proceed.

In my experience there's always at least three different ways of doing the same thing, and normally there's very little to choose between them in speed and Efficiency. My advice is to go with the method you know and understand.

It's a bit like if you are a passenger in car and you realise the driver is going the long way round, you are aware of a shortcut. You could say why don't go this way turn left here, right there and go that way. However you would probably be safer a letting the driver follow the route they know well, probably a lot safer!

 

[DBApprentice]

Thanks for the explanation, Uncle Gizmo, I will consider that and the article about "Excel in Access" really teaches a lesson - I am a Excel coming to Access.

Indeed I am having problems with the design of my very first DB in Access project and you are more than welcome to jump in if you wish.

The thread is: https://www.access-programmers.co.uk/forums/showthread.php?t=303058

 

[DBApprentice]

There have been some interesting discussions here on this subject, see this thread:-

https://access-programmers.co.uk/forums/showthread.php?p=1110510#post1110510

Uncle Gizmo, that link is not working anymore.

 

[Uncle Gizmo]

It's working for me and I tried it incognito and it still worked.

https://access-programmers.co.uk/forums/showthread.php?p=1110510#post1110510