Software

Let Access choose your data type format

Sometimes it is hard to tell which data type format to select for an Access field. For example, if your field's data type is Number, should you select General, Percent, or Integer? Also, which field size should you use? Rather than guess, let Access decide for you when you create your table in Datasheet view.

Sometimes it is hard to tell which data type format to select for an Access field. For example, if your field's data type is Number, should you select General, Percent, or Integer? Also, which field size should you use? Rather than guess, let Access decide for you by creating your table in Datasheet view.

For example, say you create a Sales Tax table for your Orders database, listing the sales tax rate for each state with which you do business. Follow these steps to set up Access to figure out the fields:

1. In the Orders Database window, select Tables under Objects.

2. Select the New button and then select OK.

3. Right-click Field1 and select Rename.

4. Type State.

5. Right-click Field2 and select Rename.

6. Type Sales Tax.

7. Click in the first cell in the State column and type NJ.

8. Press [Tab].

9. Enter .07.

10. Click in the second cell in the State column and type NY.

11. Press [Tab].

12. Type .08.

13. Click in the third cell in the State column.

14. Click the Close button.

15. Click Yes.

16. Enter Sales Tax in the Table Name box and click OK.

17. Click Yes.

To view the data types Access has chosen, click the View button in the View group and then click Design View. The Data Type column shows that Access has chosen the Number data type with a field size of Double and the default format of General, which corresponds to the sample data.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

20 comments
dnbruce
dnbruce

do some planning th work out what is going in the file. In table ddessign view select the data type or feild sixe proerties and press F1. Access help will educate you about your options.

ssharkins
ssharkins

I have used this feature when I needed to add quick test data, and it came in handy. I've never let Access apply data types in a working table for me. I can't honestly say whether it does a good job or not because I've never put it to the test. Now, I'm curious just how well (or bad) a job it does.

Tony Hopkinson
Tony Hopkinson

change it to what it should be. Another DBA for MBAs tip from TR. Brill. More work for me when I have to fix it. Edited to give a meaningful subject line. Which MBA came up with the re-... bit ? Damn stupid.

ogils
ogils

If you don't know what data type it should be either ask those for whom the db is for or don't get involved in db creation

ssharkins
ssharkins

Thanks for the helpful advice -- this is a great idea and I wish I'd thought of it myself. There are also a number of great resources for learning about data types -- I hope everyone feels free to post links.

ssharkins
ssharkins

Not everyone trying to use Access is a DBA or even a developer. Many are totally lost in the software and yet trying to learn enough to use it. I really can't say whether MA's advice would help or hinder these folks, but I did appreciate the response about using F1 to learn more -- good advice. In the end, the feature's there. Apparently, MS (and MA) recognizes that a lot of people using Access need help. I've often wondered if the feature should even be available. What do you guys think?

boxfiddler
boxfiddler

Access doesn't exactly default to the most useful data type in some cases. Doesn't strike me as a good idea...

Shellbot
Shellbot

ok, if just doing a quick prototype or something, whatever, but in an actual working database?? If ye can't even get your datatypes nailed down, don't bother, get someone else to do it for you. Better yet, look it up and learn what needs to be done..if your using Access enough to be creating tables, its something you just might want to know.

HanksComputer
HanksComputer

Could not have said it better myself... People who do not know what they are doing cause more problems than they solve; Oh, wait a minute that is what keeps me in business...

Tony Hopkinson
Tony Hopkinson

We've got enough amateurs running around creating island databases as it is, the least they could do is get the damn data type right. The help these people need is in the how and why of database design, not some lobotomised default, so they can avoid learning about it.

rob mekel
rob mekel

know what datatypes to use ... Use as much different types you can get :^0 To make it even better use different types of datatypes for the same data in the different systems :^0 :^0 If you're afraid to loose you're job ... on the shortterm you won't be sacked. Longterm: get the H3ll outta here!!! and pay your salary back incl. your bonus !!! The shortterm will be used to tell the new guy to unpack the spaghetti you have made. :D Rob

ssharkins
ssharkins

I just wanted to say thank you for your thoughtful and generous reply. :) Obviously, you have a good feel for the situation. (I couldn't reply to the actual response -- limit has been reached there.) I don't consider myself a high-end developer -- the truth is, that kind of work gives me a headache -- just too much stress. What I truly enjoy is finding easy, approachable solutions that the average user can apply with the right instructions. Hearing a developer say, "it can't be done without code" is like waving a red flag in my face. ;) Invariably, I do use a little code, but even a beginner can too, with the right instructions. I know that employers purchase software, but they don't always take training seriously. That's why I'm so passionate about providing solutions and answers to those that really need it. Developers don't need me, and I like being needed. :) I don't see users as a pain, which, from your response, I don't think you do either. :) Thank you again for your gracious response. I bet your users love you! ;)

Absolutely
Absolutely

"Some of them don't even KNOW that they need to learn about relational theory and data types." They don't "need to learn about" any such things, I agree. I actually meant in that post to be derisive of the app, not the user, but if that was unclear to you, I should probably expect that it's also not clear to the hypothetical end user, and be more tactful. As a developer, I agree about the buck stopping with me, not the end user, for malfunctions I program. And in light of your comments about "nice jobs", I guess taking a little extra time to explain some "boring technical garbage" to an end user is a small price to pay -- for 6 figures. :^0

ssharkins
ssharkins

I have seen Access sing a few times -- in the hands of someone with the right knowledge, you can make Access do just about anything. I know developers using Access to do things it's not designed to do. :) It's a powerful tool in the right hands.

ssharkins
ssharkins

I know freelance developers who won't work on an existing application -- and I understand their decision. Just trying to help a reader debug something is often difficult and time-consuming. On the other hand, try not to be so harsh on folks who don't "take the time" to learn the theory -- you're looking at it from the other side instead of theirs. Some of them don't even KNOW that they need to learn about relational theory and data types. They're difficult concepts at first and not everybody's going to pick them up on their own. My personal feeling on this is that employers who supply software need to take responsibility for training those required to use it. The truth is, if an inexperienced user creates a database that generates bad data, they only hurt themselves and ultimately their company. They certainly don't hurt me or Microsoft. And, if you guys, as developers create custom applications that these casual users mess up, well then, shame on you... it's your job to make them airtight so that users can't alter them. So, try to lighten up a bit on the casual users -- if nothing else, they keep a lot of us in nice jobs. :)

Tony Hopkinson
Tony Hopkinson

Paradox, Dbase. The three scenarios I ran into were the MDs nephew, who knows computers. The rcent grad who went from from the hospital, bed, patient exercise to an enteerprise app and the power user who did something very useful that became very popular. In essence they all started with a desktop database, bashed something together, for usually one user, may be three or four and then tried to scale it up. If they had known anything about databases, they would have put this in the non-starter category. As a Power user tool it's excellent,but for building systems ... I didn't mind getting the work, the people who were paying my wages weren't happy though. I was usually brought in intially to fix the desktop system, not reengineer it. Initially they took my inability to fix it as a lack off skill on my part. :(

Absolutely
Absolutely

Therein lies the problem! Relational databases already provide a very powerful structure for organizing data via key relationships. The idea that learning a bit of SQL is too difficult or time-consuming is just ridiculous to somebody who knows it. I might take a contract tidying up a mess made by an amateur Access "Developer", but I'd rather work on making apps correctly, the first time.

ssharkins
ssharkins

How many of you truly have to "fix" other people's mistakes -- I hear this grumbling about it, but what's the real problem -- software in the hands of inexperienced users or not enough money and technical expertise to train humble users? What's really the problem? Keep in mind that Access is supposed to be a desktop database for casual users. Lots of developers do make a good living creating custom applications with it -- but people making mistakes would only help that market -- sounds like an opportunity to me -- send a few of those inexperienced users to me. :)

Shellbot
Shellbot

Use as much different types you can get hahahaha...thats one approach i've never thought of..

Editor's Picks