Advice on a simple inventory database in access 2

[mbaldridge]

Hi, As you can see I am new to the tek-tips community. I am currently working on a project at work where I must converge 6 spreadsheets with 4 columns of information (1 unique identifier) and about 20,000 rows a piece into one easy to navagate and search database.
I cannot simply paste these all into one Excel spreadsheet because it will overload any computer here.

Pardon me if my questions are rather elementary, but I just want to do the best job I can and build a database that can grow with our company. Where do I start when building a database? Does anyone have a simple solution?

Thank you in advance for your help. Please let me know if I have left out necessary information.

Mitchell D. Baldridge

 

[mbaldridge]

Thanks, again, for your help Mr. Rhodes. Could you forsee it to be a potential problem that over the years the format of this unique identifier in the fourth column has changed in format.

it could look 2 ways for the most part

2005-present - D1DC0512345
1999-2004 - 1032345

I have not tried moving the last column to the first yet, but initial imports seemed to have been thrown off by this.


Thanks again for your help,

Mitchell D. Baldridge

 

[lespaul]

as long as it is unique across the board you shouldn't have any problems importing it. Now if you have let Access decide what the field types should be for the import then you will have a problem. Access will have looked at your 1999-2004 data and decided that the field should be a number and when you try to add the 2005 and later data, they aren't numbers.

Be sure that the field is a text field that can allow the maximum number of characters (above you have 11) and then access will allow you to import both 1032345 and D1DC0512345 into the same field

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[mbaldridge]

Thank you Leslie

 

[BobRodes]

Also, your primary key will need to be the first column, which is why I suggested the change. You could do it in the import tool, too, but I figured you alreay know how to transpose columns in Excel.

 

[MichaelRed]

" ... your primary key will need to be the first column ... "

Really? I don't believe I've seen that requirement ... could you provide a reference? I am quite able to generate rimary keys" in access without including the "first column".






MichaelRed

 

[PHV]

I don't believe I've seen that requirement
I'm with you Michael.
Furthermore a PK is even not mandatory (but preferable ...)

 

[mbaldridge]

Like you had speculated, Mr. Rhodes, my primary key is not so unique. Typically, the problem is that in making the spreadsheet, at the end of one box to the beginning of the next box it goes:

2070123 | box 1 | smith | bob
2070124 | box 1 | johnson | edward

| Box 2|

2071215 | box 2 | michaels | robert


There is this separation between each box, which does not mean a whole lot in the context of what I want my database to do..

The question becomes, do I need to cut and paste all these out, or can I simply ignore the errors in the spreadsheet and go on with the process?

Thank you, as always

Mitchell D. Baldridge

 

[BobRodes]

<could you provide a reference?

Certainly.

http://en.wikipedia.org/wiki/Common_sense

HTH

Bob

 

[MichaelRed]

not to put to fine a point on it, but I am not confidant of the certification of the reference, could you provide one from a more reliable source? in particular, since I seem quite able to generate and use Primary Key indicies which do not follow the rule ...




MichaelRed

 

[SQLSister]

It would actually be a bad idea to make the primary key the first column if it was not there when the table was created as you never, and I mean never, want to change the order of columns in a table that already has data.

"NOTHING is more important in a database than integrity." ESquared

 

[BobRodes]

<never, and I mean never
That, and always, are the two concepts that are never completely true in the IT world...

 

[BobRodes]

<in particular, since I seem quite able to generate and use Primary Key indicies which do not follow the rule ...

This reminds me of the story of the American farmer who successfully sued a ladder company for failing to adequately notify him that his ladder was not to be set in a pile of cow manure before he used it to go up on the roof, and thereby fell, creating various torts, malfeasances, and damages due to the clear negligence of said ladder company...one may mention that common sense isn't a recognized doctrine in the American legal system either.

Since you appear to wish me to explain my position further, and we're having such fun together: we are using the word "need" with different semantics. For example, I might suggest to you that you need to brush your teeth at night, providing the following

http://www.frsd.k12.nj.us/autistic/Social Stories/Pages/brushing_my_teeth.htm

to support my point of view. Applying your arguments in this context, you might well express your lack of confidence with the reference, given that you have taken the trouble to go to bed without brushing your teeth and found it entirely feasible. I would simply repeat that you "need" to brush your teeth before you go to bed, and I might add "at least the ones you want to keep."

I hope that you may see the analogy and that such insight on your part will serve to clarify my position with respect to making the primary key the first field in the table.

Bob

p. s. Although it of course has no bearing on the argument at hand, I'd also like to correct your spelling of the word "indices," because it might make me look smarter than you if I do, thereby indirectly lending weight to my side of the discussion.

 

[BobRodes]

<It would actually be a bad idea to make the primary key the first column if it was not there when the table was created.

All kidding aside, let's say I imported a database from a text file. The result would be an unkeyed table, which I then would have to massage in order to bring it into a database. I might very well have a keyed table whose fields were in a different order from the unkeyed one, and then iterate through the latter, placing the fields into the former in the new order. In effect, this would be a reordering of the fields, and I submit that such a reordering when moving data from one place to another is quite common.

Now, my reasons for keeping the primary key as the first one are twofold: first, it's a convention that makes the database easier to maintain, and second, some DBMS's (Paradox, for example, at least the older versions that I worked with) don't allow pk's to be in any other position.

If I encountered a database that didn't follow this convention, I would strongly consider rearchitecting it so that it did. The main reason I'd want to do that is that I would see this as indicative of the likelihood of other more significant departures from best practices, and I would therefore want to overhaul the entire application.

However, I do certainly agree that one should not simply go into a database, open a table in design mode, alter the order of the fields, and expect everything to just run fine. To reorder the fields in a database is a major undertaking, particularly if the database already contains production data.

Bob

 

[mbaldridge]

as long as it is unique across the board you shouldn't have any problems importing it. Now if you have let Access decide what the field types should be for the import then you will have a problem. Access will have looked at your 1999-2004 data and decided that the field should be a number and when you try to add the 2005 and later data, they aren't numbers.

Be sure that the field is a text field that can allow the maximum number of characters (above you have 11) and then access will allow you to import both 1032345 and D1DC0512345 into the same field"

Leslie- How do i make sure that access imports the field as text. I have made it simple text in excel and saved the spreadsheet, but access still does not want to import all the data in "D1DC0512345" format, just the numbers.

Thanks for your help,

Mitchell