X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Brand New Member - Help On Design Of Database, Access 2016    
post May 7 2019, 05:10 PM

Posts: 4
Joined: 7-May 19

Man, am I glad I found this community. I have read the rules and the new user's guidelines as well as the 2 chapters of Database Basics, good stuff. I even tried to search this site and as a newbie, I'm unsure of which questions to ask or how to ask them!

I am attempting to design a database and related queries, forms and reports to handle warranty records for my company. We currently do this with a few huge 3-ring binders, Quickbooks and our CRM software. We need to have this information in one place and be able to add records, search and produce reports. I have no experience with Access, my only DB experience is from years ago when I did some work in DBaseIII+.

We manufacture a specialty tool, and we have two production lines: Manufacturing and Factory Reconditioning (Reman). When a new tool is built, it gets a unique serial number. If that tool fails in the field, we send a replacement tool and, when we receive the defective tool, we rebuild it and use the same Serial Number with an "R" suffix. This "R" suffix can easily be changed to suit the needs of our proposed database, it just happens to be the way we are doing it now.

Database Goals:
Search or extract records based on customer name (or number) for number of warranty claims and original shipment date to verify warranty is active. The original warranty is 12 months from date of shipment.
Search or extract records based on serial number of the product to determine how many times it was reconditioned and to whom it was sent as a replacement. The Reman warranty is 2 months from date of shipment, except if the original warranty is still in effect. Short version is original warranty takes precedence.

The size of the database at this point would be around 500 records but that increases each time a new tool is shipped or a Reman is reconditioned and shipped.

I have been trying to figure this out in Access for the past 3 weeks and I feel that I must have a gross conceptual error in the way I'm trying to design the fields and records. I don't need a member to tell me how to write it, I would like some suggestions for some ways to lay the table(s) out to meet the goals and I'll take it from there, I certainly want to learn how to do this.

The lack of a unique identifier for each time a unit is reconditioned seems to be part of the problem. I have tried one large table with 4 fields for each serial number (S/N), S/N; CustName; Shipdate; Failcode (reason for failure); S/N2; Custname; Shipdate; Failcode; repeating for each time the tool is reconditioned.

I also tried multiple tables, one for OriginalMfg, Reman01, Reman02, etc.

No doubt I've left out some key information but I can expand or detail any of the steps as needed.


Go to the top of the page
post May 7 2019, 05:15 PM

Access Wiki and Forums Moderator
Posts: 76,520
Joined: 19-June 07
From: SunnySandyEggo

Hi Simon,

Welcome to UtterAccess!

It might help if you could post a shell version of your database with maybe some test data in it, so we can try to see if the design is adequate to the goals you have described so far. If you're not allowed to post an attachment, images and examples of the table structure should work as well.

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post May 7 2019, 06:50 PM

UtterAccess VIP
Posts: 2,874
Joined: 12-April 07
From: Edmonton, Alberta Canada

Ok, a few tips:

I did some work in DBaseIII+.

Great! It means you have some idea of tables etc.

There is a lot of issues here.

So, you can think of tables much like Excel sheets. (Now, I am about to get roasted by my fellow peers for saying this!).

So to be clear, I saying that some concepts you learned in Excel can and do apply to Access. But make no mistake, how the general approach to data management in Access is VERY different then Excel.

But, in regards to dbaseIII, or Excel, you at least grasp the idea of rows of data with columns.

Ok, so to start this whole thing out, we want a customer table. (Let’s not worry about ANY thing else at this point).

You want to build a nice customer form.

From that customer form, you can now search + find any customer. For just starting out, placing your cursor in the customer field, and going ctrl-f to find will “get you” to the one customer.

So, the first baby step would be above.

Now, once you get the above working?

Well, now we can start to bring the magic of Access and what we call relational database power. Don’t let this fancy term scare you, but by breaking this down into separate goals and parts, we can attack one part and problem at a time.

Now, once we have that customer form displaying the customer, we can THEN extend that form to display all the warranty records for that ONE customer.

We can do this as many ways as there are flavors of ice cream. One of the really big challenges in Access is that it is a VERY powerful tool. Because of that power, then Access is a near blank canvas as to how you want to this work.

We could on that customer form have a button we click on, and that would then launch a form to display all of the warranty events (information) for that customer.

Or, in fact, right on the existing customer form, we can have access display a “list” of existing warranty claims.

I don’t have a warranty example, but here is a screen shot of a customer, and their booking events.

So, note how the top part of the form displays the customer information.

But, note how I have “repeated” data for the booking events.

In your case, that list could be past warranty claims. And note the “edit booking” button. That just launches a nice big form to edit all of the booking information (or in your case all of the warranty information).

So a few tips:
In above, there are two forms. The main form to display the customer, and then another form to display the booking (or your warranty information).

That 2nd form is based on a table. It is what we call a sub form in Access, and it allows display of “related” data on ONE form.

(But in fact the form is two forms).

So, what you can do is build the customer table. Build a customer form. (But leave room on the bottom part for your warranty list information).

Note that we not (yet) created the warranty information table.

So, we start out with:

Create our customer table.
Create a form to edit the customer table.

Now, we can then create a table for the warranty information.

And we can create a form to “edit” the warranty information.

And we can create a form to “display” as columns of data the warranty information. (Just like I did in above for the booking).

Now, last but not least?

I am for ease of getting going suggesting you make the customer table, customer form, and get that working.

However, near NUMBER ONE suggesting here is that you should on paper or whatever, figure out the tables you need BEFORE you ever fire up Access and start building tables.

So, you should thus then outline the tables you need, and then start working with Access.

With the above simple approach, we have two tables:


And, as time passes, you find that as you build up this system, you may well wind up with “many” tables – not just two.

So, we likely need perhaps some product or part table, but right now, we have:

Warranties – a table of warranty claims. This table will be “related” back to the customer’s tables.

With a correct for design, then when you search + find a customer, then that customer form can now display all warranties for that customer (or allow you to add more to/for that customer).

So, information like “serial” number etc. will not go in or be part of the customer table – such information goes in the warranty table.

So, I hope the above helps you get your feet a bit wet.

The hardest part here is going to be figuring out the tables you need, and then how to “link” or what we call “relate” the tables together.

Access is a great system for your needs. And even better, is you can setup this up to be multi-user, so more than one workstation can now look at the data while you on the phone etc. with a customer, and you don’t have two people fighting over the one binder of information!

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
post May 7 2019, 09:43 PM

Posts: 4
Joined: 7-May 19

DBguy, Thanks for the welcome, I'm happy to be here.

I figured you guys and gals would need clarifying examples, I'll get a post up on that in the next couple of days. Unfortunately, I did not save each attempt as a discrete file so I can show the progression, but I'll try to reconstruct or at least show the latest iteration.
This post has been edited by SimonSmith: May 7 2019, 09:45 PM
Go to the top of the page
post May 7 2019, 09:47 PM

Posts: 4
Joined: 7-May 19

Albert, thanks for the detailed post! It will take me a bit of time to digest it and try out your recommendations. I'll get some examples up.
Go to the top of the page
post May 7 2019, 10:03 PM

Posts: 4
Joined: 7-May 19

Albert, I'm picking up what you are laying down. This is the kind of advice I was looking for: A different way of looking at it.
Should I build the Customer and Warranty shells first? Then start in on the form? As you can see from my questions, I really don't know what I'm doing at this point! So much to learn and I'm excited to become proficient (or at least adequate).
Go to the top of the page
post May 8 2019, 08:08 PM

UtterAccess VIP
Posts: 2,874
Joined: 12-April 07
From: Edmonton, Alberta Canada

Should I build the Customer and Warranty shells first?

yes, but I shall note the term "shells" might not be the best, or that is simply the best term you have (being all new to this).

In fact, I recommend that you don't build or worry about the warranty part of the system.

The first step will be you and your team of engineers and software developers to build a "customer information" system.

Welcome to the Lockheed skunkworks team.

Now, the above sounds all fancy, but the first steps are:

You going to build and design the customers table.

And for all tables that you build now, or in the future? The first colum will be ID, autonumber, primary key. ). This rule is to be followed for all future tables you build.

So, the first skill set is to build that customers table.

You will have to learn how to use the table designer, and add columns (fields) to that design. This based skill is required to use Access, and you will use this skill over and over and over when you are using Access.

Once the table is built, you can say simply click on the table, and enter a row or two of data.

Now that you have a working table, then the next step is to build a working form.

Again, that form will manage our customers. And again, this is a basic required skill set to do ANYTHING with Access.

So, the above is your first set of goals.

Once you done the above?

Well, now we know how to build tables.
Now we know how to build a form.

So, the concepts of placing controls on a form is the base skill set you gain after having built a table in Access.

At this point in time, we have a working customer form, and thus we have a working customer management system.

once you have all the above working, and laid out the form much how you want, then and ONLY then do we move on to the next step.

The next step is to create the warrenty table. Once again, ID (pk, autonumber) for first column.

All of the next colums such as warrenty date, product code, purchase data (or whatever) for a single warrenty record is to be designed.

When you have all the above, we have one more step. That is adding a relation from this table to the parent table "customers".

You do this by adding a plane jane Long number column. And this column should describe what it is:

a rather good name would be:


So, in addition to all of the other columns, (like the first column of id, and then all kinds of columns for warranty date, part number or whatever).

And, then the above standard plane jane long number column of customer_ID.

This column while just a plane jane number column is rather special: It is the column that will be used to relate back to the parent table (the customers table).

Once you have all of this table designed, then you will fire up the relationships manager, drop in table customers, drop in table warranty.

And you use drag + drop and drag the pk "id" column from the customers tables to the warranty table - but drop the ID onto our plane jane customer_id column on the warranty table.

Congratulations! - you just achieved one of the greatest features and inventions of computers. You built your first relationship between two tables. The dawn of relational data base systems is a earth shattering event in our history - kind of like when the Apes in 2001 picked up a bone, and whacked on that dead animals skull!

Once you done the above, then next step is to build the warranty form. That whole process will be just like what you did for the first table, and the first form.

So, build the customer table, and the customer form.

Then build the warranty table. And that VERY delicate step of relating the two tables? Well, I would start a new post/thread on UA if you have difficult with the relationships window, but that step is easy, but also rather critical. Consider picking up a book, or watching some tutorials. But key skills so far are:

Using the table designer. Add columns, define things like City, or phoneNumer or whatever.
Using the forms designer. Add columns from above table, move things around - lay out those fields on the form (text boxes on a form).

With the above two skills, then you can start your journey here.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
post Aug 26 2019, 07:07 PM

Posts: 4
Joined: 23-August 19

I am following this thread, and trying to apply it to what I am building.
I have a tblCustomers (tblStudents for me). I added and linked one lookup table for provinces.
I have a working frmStudents.

Next I need to think about what table is next. I need courses, instructors, and very important: course events. I need to put course cost in somewhere too. and payment info for the customer.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th November 2019 - 02:00 AM