General Questions

[Dave14867]

Hello all,

I have made a handful of databases and they seem to work well. It has been a great learning experience and many of you here have helped me immensely. Access is a very powerful application and you folks know it extremely well. I have made mistakes and you have shown me how to correct them, and possibly more importantly, why I shouldn't do things a certain way and the correct way to do them. For me, it has been a great experience as I have learned so much and those who have assisted me on this learning journey have the patience of saints sometimes because at first, I just didn't understand why the way I tried to do things wouldn't work or wasn't the best way but with your guidance and trial and error, it has become clearer.

I am going to try a larger endevour now and have some general questions that I am sure have no hard/firm answers but as a general rule may have an answer.

1. What should be the max number of fields in a table. - I have read that a table should be narrow and tall, of course a table will get taller as records are added but how narrow is narrow?



2. Why are "Lists" bad in a table field and should only be placed in a Forms text box?

3. How many tables is too many?

4. Naming nomenclature - I know you shouldn't have spaces in table names but when you look at templates like Northwind it has spaces, is this just because of the underscore that gets placed in the table name by code later?

5. Is it better to place a query in code or just have a stand alone query in the database and call on that, what are the benefits and downsides of each.

6. Is having more that one yes/no field in a table violating normalization?

Hopefully other novices can also learn some of the mistakes to avoid by your answers.

Thanks

Dave

[isladogs]

There are no right answers to several of your questions
Use as few fields as possible but as many as needed. Similarly for tabled.
What matters is that the tables are normalised

There are some limits in Access though it's unlikely you'll hit many of them..
See https://support.office.com/en-us/art...8-98c1025bb47c

By lists do you mean multivalue fields? If so, they will cause you major issues when searching or filtering. AVOID THEM.

MS sets many bad examples in several of their templates including naming conventions.
If you use a consistent naming system with no spaces or special characters and CamelCase, you can't go wrong.

I prefer to use query SQL in code so everything is located in one place.
For the same reason I never use macros or embedded macros but that's personal preference.
There are some who only use queries.
However you can do some things in SQL that aren't possible using the query designer.
Sometimes it is necessary to use a saved query as part of another query or SQL so its not possible to be too dogmatic.
You will often see comments that queries are faster than SQL as Access optimises the code when the query is first run.
However its unlikely you will notice a difference in most cases.
Indeed I've sometimes found SQL to be faster

I have several tables with more than one yes/no field. Depending on what the fields are, the tables may still be normalised.
The alternative is to have one yes/no field with a description text field instead.
This means more records and fewer fields

Hope that helps.
Others may disagree with some of my comments.

[Dave14867]

ridders52,

Yes I was referring to multivalue fields in tables.

I will use only CamelCase in the future, I have run across the space issue in past and it is aggravating.

Regarding Querys vs. code, I have seen both used, but since SQL isn't one of my strengths I rely on Query's, but I could use the SQL built with a query and put that in code, I can see advantages to that, it just the Query builder is quite easy for me to use most times.

Thanks for the input.

Dave

[isladogs]

I often use the query designer then convert to SQL for use in the VBE.
You may find my 'SQL to VBA and back again' utility helpful. See http://www.mendipdatasystems.co.uk/s...ain/4594398120

As well as avoiding MVFs, I strongly advise against the use of attachment field and lookup fields at table level.
All belong in Room 101

[GinaWhipp]

There are no right or wring answers, there are only preferences. That said...

1. What should be the max number of fields in a table. - I have read that a table should be narrow and tall, of course a table will get taller as records are added but how narrow is narrow?

Access Specifications
https://regina-whipp.com/blog/?page_id=42

While Tables should be narrow not wide that plays more to data normalization. So, as long as your Table is properly normalized wide could be okay. You just want to be carefule you are not trying to create an Excel spreadsheet in Access.

2. Why are "Lists" bad in a table field and should only be placed in a Forms text box?

http://theaccessweb.com/lookupfields.htm

3. How many tables is too many?

No such thing. Too many is only restricted by the size of the database. I have databases with 300+ Tables. Note, a good deal of those are what I call look-up tables. I call it my 3-to-1 rule. Which means an average of 3 look-up tables per main table, i.e.

tblAssociateProfile (Main Table)
tlkpShifts (Look-up Table)
tlkpStatus (Look-up Table)
tlkpStates (Look-Up Table)

While some Main Tables will have more than 3, some could have 1 or none at all. However, in the end I always end up with more Look-up Tables. This also gives your End Users (and you) control over the values in drop downs.

4. Naming nomenclature - I know you shouldn't have spaces in table names but when you look at templates like Northwind it has spaces, is this just because of the underscore that gets placed in the table name by code later?

Northwind is a horrible example of naming nomenclature, my preferred method
https://www.access-diva.com/d1.html

5. Is it better to place a query in code or just have a stand alone query in the database and call on that, what are the benefits and downsides of each.

I use both, however, I do prefer SQL for action queries, UPDATE, APPEND, etc. because they leave less of footprint and hold the size of the file in check, as well as, running faster. Should also note that you can do a lot more with SQL then with the standard query interface. Allen Browne wrote a tool to help the novice convert querydef's to SQL and I added a way to also use it for action queries...
https://access-diva.com/blog/?p=241

Here's a list of some other tools that can make your coding life a little easier...
https://regina-whipp.com/blog/?page_id=1198 (Most are free but there are some that are not, still worth every penny if you plan to do this for a living.)

6. Is having more that one yes/no field in a table violating normalization?

I don't see a problem with using Yes/No (BIT) fields but I do use them in moderation. IMHO, I think too many of them may mean you haven't properly normalized.

I have a list of what I call Database Standards that you might find helpful.
https://regina-whipp.com/blog/?p=102

[isladogs]

Hi Gina
Good to know we're thinking on the same lines! See my posts 2 & 4.
The utility I mentioned in post 4 is based on Allen Browne's example but (with his permission) adds extra functionality

[GinaWhipp]

I saw that one but have not downloaded to take a peek at it.

[isladogs]

Dave

In view of your questions, thought you might be interested in these database statistics for my largest app used in various UK schools. Its BIG!



The database statistics app I created to do this is available as a free download from my website: http://www.mendipdatasystems.co.uk/d...ics/4594424201

Alternatively Access add-ins like MZ Tools contain very similar features

[Dave14867]

GinaWhipp and Ridders52,

Thanks for the feedback, I will look at all the links you suggest.

Ridders52, that is a large database and I guess that takes care of that question.

Ridders52, the new project I am starting is a redesign of one that you have assisted with recently. It does work but I think it could benefit from a redesign as I have learned a lot in developing that one and others. I do this for my own benefit and to make my job at work easier, I don't get paid to do it. I never realized before just how powerful Access is and it's capabilities and I know that I so far have just dented the surface

Thanks

Dave

[isladogs]

Hi Dave

It is a large split database but the 143MB file size is just the Access FE. It has 3 BE files the largest of which is a SQL Server fie of about 1.5GB after around 10 years of use. As the free Express version of SQL Server has a 10GB limit, it should see out my lifetime.

If you wondered why there's only 1 relationship, that's because the tables and relationships are in the BE files

Like you, I started developing Access apps unpaid to help with my work role (I was a deputy head in a large secondary school). Many of these then grew into apps used by all staff, then became commercial apps used by several schools.
After 20 years, I'm still only scratching the surface of what Access can do and learning new things.

Good luck with the redesign. We'll be here to help if you need it

[Dave14867]

Ridders52,

How do I build a "Select Distinct Row" query in the query design window?

Thanks
Dave

[Dave14867]

Ridders52,

I have run into a proble with a query again (big Surprise).

In the attached file, if you open the "Purchase Orders" form and try to enter a new line to the order, it can't be saved because the PartID field isn't being populated and I cannot figure out why. I believe it has to do with the query but I cannot figure out what is wrong. I copied the query from another working database which I got the template from the internet and have modified the file to suit my needs. I modified the query to coincide with the new table fields but I am missing something.

Cheyenne.zip


Edit - I figured out what the issue is, this is fixed.


Thanks

Dave

[isladogs]

Hi Dave

Use the query property sheet
Set Unique values = Yes for SELECT DISTINCT
Or set Unique Records = Yes for SELECT DISTINCTROW

You could also just type DISTINCT in query SQL view

NOTE: It doesn't need to be me replying
however, I'll try & have a look at your file tomorrow ..unless someone else does so first

[Dave14867]

Ridders52,

yes I know it doesn't have to be you but you always seem to be there. The second issue is fixed, I figured out what was wrong.

Thanks

Dave

[isladogs]

LOL!
Glad you got the other issue fixed as well. I'm logging off now - its VERY late here in the UK