Database design

[Kill_Switch]

Hello fellow access users. I'm new to the access world but have already realized its potential, and so I took a course to learn the basics of access, and I've been tasked to try and create/modify a database for work. In which the user that created it did not make it edit friendly. And guess who has been asked to create a new database. And here's my idea, but I'm a little unfamiliar with it so any step into the right direction would be awesome.


I work for the Canadian Forces, and I require to create a database to keep track of what parts have been ordered and received for each aircraft (3) and some support shops. (ALSE, TOOL CRIB, OTHER)

I am Going to import an excel document, that has the record of what items are stored into which vidmar. Labels for the sheet is. (NSN, Part #, Description, Location) I would like to search that tables items, based on NSN, and Part #. However no duplicates of NSN's are allowed.

I'm sure alot can be summed into the screens below and in next post (max of 5 uploads had to "double" post)

I created a table with the values listed in the newdemand.gif

• 
  CustomerControlNumber - Auto Number
  MIMSReqNumber - Text
  SupplyRecord Sheet Yes/No
  Work Order/Project/Activity - Text
  Date Required - Date/Time (medium date)
  Priority - Text
  Serial Number - Text
  Originator - Text
  Telephone - Text
  Date- Date/Time (medium date)
  Special Instructions-Memo
  Qty - Number
  UOM - Text
  type - Text
  StockCode - Text
  Description - Text
  Trade - Text
  Received By - Text
  Date Received - Date/Time (medium date)
  Returned by - Text
  Date Returned - Date/Time (medium date)
  ClassDate Returned - Date/Time (medium date)
  Z# - Text
  Part # - Text
  Notes - Text
  Requested By SCA - Text

However I need to look into linking that any personnel that get added or deleted are ammended on the newdemand.gif screen.

 

[Kill_Switch]

The "parts rxd.gif" is the form that gets generated when you click on any buttons from the "search demands.gif" I'm assuming that each button will perfom a search from the overall data "supply data" table and spit out a report based on the criteria "130-333" Which is the aircraft.

 

[KathyL]

Most databases can be modified with new improved design or forms. I’d suggest taking what was started and work to improve it. Make a copy, place it in a development partition, and when your changes are ready, export those changes over to the production version.

By your word of “linking”, I am assuming you mean join properties between related tables.
Yes you can import data from an excel worksheet into an existing table or a new table. After the import, you can place an index on fields which either have no duplicates, or duplicates are ok.
If you design a form, the standard Access find (binoculars) can be used on any field, or you can place a command button on the form which does a find function.
I’m sorry, but I didn’t look at your .gif files.

 

[Kill_Switch]

Most databases can be modified with new improved design or forms. I’d suggest taking what was started and work to improve it. Make a copy, place it in a development partition, and when your changes are ready, export those changes over to the production version.

By your word of “linking”, I am assuming you mean join properties between related tables.
Yes you can import data from an excel worksheet into an existing table or a new table. After the import, you can place an index on fields which either have no duplicates, or duplicates are ok.
If you design a form, the standard Access find (binoculars) can be used on any field, or you can place a command button on the form which does a find function.
I’m sorry, but I didn’t look at your .gif files.

That sums it up. I fiddled around with it and got it working. Well okay for now. Still working on making it flow right.

Thanks Kathy.

 

[Kill_Switch]

is there a way to get away from the conventional looking command buttonsand creat my own, or does that go in depth if vba code

 

[speakers_86]

One option is to simply use an image (LINKED!) that has an onclick event. The mouse will not change on hover, but I have seen code here that makes the mouse change on hover. In fact, I asked that question once. If you search for posts I have started you can find it easily. It was not that long ago.

 

[speakers_86]

Here it is. I have not tried the code yet. I figured it doesn't add enough value for me to go through the trouble of making it work. But hopefully it helps you!

 

[The_Doc_Man]

In many cases, when you take a course in database utilities, they tell you how to use the utility, not how to design the database. This is not so much a failing of the teachers and more that it is just a complex subject that takes more than minimal study.

Therefore, I cannot presume that you have studied normalization. Nor did the GIF's tell me. Nor did your discussion. AND, stating that you have to get your data from an Excel spreadsheet as a starting point tends to point to flat-file thinking for the initial design. If you in fact have studied database normalization, then forgive me for being overly pedantic. However, if you have not, then you need to do some serious study on that topic before you get in over your head. There is nothing more unwieldy than trying to work with a badly normalized database when you are trying to get things done and changes made quickly.

Using your favorite search engines such as Yahoo, Google, Ask, or MSN's offerings, look up Database Normalization. In Access, you can get short discussions from the Help files on the topic "Normalization." (Because in Access, there is no other kind of normalization.) If you go to www.wikipedia.org, you can look up Database Normalization and get some decent reading.

Use the full-blown search after those two articles, but ignore articles that don't come from reputable colleges or universities you recognize, or from database vendors you recognize. Once you've read enough articles that the next couple of articles don't tell you anything you didn't already know, you're ready to revisit your design. Despite the amount of subject material out there, this diversion might represent no more than a one- or two-day delay.

OK, why normalize?

(1) Minimizes the size of the data you must store because it eliminates redundant storage of data fields in favor of just storing pointers in tables that refer to, but don't actually define, the particular field. Something that in Excel just cannot happen. I.e. if you have a parts table, you define everything about the part. But in any table that doesn't define parts, just use the part unique number as a pointer and look up what you need in any of several ways.

(2) Minimizes the maintenance work you must do. Because it uses Julius Caesar's old "divide and conquer" strategy, it assures that when you work on something, it is the smallest something you need to touch for the relevant subject matter - whether we are talking parts or projects or personnel. When dealling with personnel data, you never have to touch projects directly. (There might be something called a JUNCTION table that you would need to consider, but otherwise, everything is isolated.)

(3) With a good normalization, you can turn the database into a model of your actual business flow, using queries, forms, reports, and the occasional VBA segment to represent transfers of data and/or data processes from point A to point B. This is not the only thing that Access does, but it does a pretty good job of data modeling for a business flow model. And that might be a great thing, too.

(4) Normalization preserves useful data from being deleted when related but separate records are deleted. That doesn't always happen with Excel.

Access works best if you can get to 3rd-normal form for all tables. (You'll understand that requirement if you study normalization as I suggest.) Access can support 4th-normal and 5th normal forms with some effort, but usually you can stop at 3rd-normal.

Once you normalize to 3rd-normal form, Access can help you by enforcing the natural relationships associated with your data model. That becomes VERY useful.

The down side of normalization is that if you are more accustomed to spreadsheet layouts like are used in Excel, the mental leap can sometimes be staggering. However, once it hits you, it is like a thunderbolt and doesn't take long after that before you start thinking naturally along the lines of normalization.

Sort of like the first time you go to an IMAX 3-D movie after too many years of looking at flat-screen presentations.