Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 416
  • Last Modified:

Creating Table in Access - Best Practices

Hello,

When I am creating tables to store data – for example product specific in a table – what are some good or best practices I need to do in the Field Properties > General for all the fields within a table?
Please suggest what best practices you have learnt from past experience.
0
Rayne
Asked:
Rayne
  • 4
  • 3
2 Solutions
 
RayneAuthor Commented:
0
 
RayneAuthor Commented:
Settings some good to have field properties for maintaining the  data within the table in the long run
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
1. Choose the appropriate data type.   ie. don't store a number in a text field.
2. Use the comment field to indicate what your using the field for.
3. Set the Caption field, even if it is the field name.
4. Make sure you set the required and allow zero length string as appropriate.
5. For field names, do not use any reserved character or word  ie. Desc is bad for a field name, because that is used for a descending sort.   'Date' is a function name in VBA.

Jim.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
RayneAuthor Commented:
Thank you Jim,

That’s a GOLD. One question>
3. Set the Caption field, even if it is the field name.
If I set up caption in the table and then create a form out of it, then will the form display that caption I originally set in the source table? Also why would need a caption?

Thank you
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
If you set the caption, it will be picked up when creating a control based on the field.

As for why, you might have a field such as SSNum, but want it displayed as Soc Sec #

# in a field name being bad because it's a date delimiter, so you don't want it there, but might for the caption.   Or "EmpID" might be "Emp ID" for the caption.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Assuming by the way that you know that embedded spaces in field names and/or any special characters is not a good idea.

Jim.
0
 
RayneAuthor Commented:
This is perfection to the ultimate, thank you :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now