Access or Excel (or neither)

[down in falmes]

The company I work for currently uses an excel spreadsheet to keep track of
requests (from phone customers) of products we do not yet produce (automotive
related). At the end of the month, the numbers are compiled, then the cells
are cleared. It's getting to the point of being unmanageable as a basic
spreadsheet (duplicate entries etc.). What I want to do is create a "sheet"
where the end users can use a series of drop downs (Model year, make, model
and product) to enter the request. The only thing I will need to tally is the
number of times a particular product is selected over a month. I am fairly
fluent in excel but new to access. Is this something that can be tackled with
either of these applications?

 

[Klatuu]

It could be done with Access fairly easily.
Let's start with a few questions. It is apparent you are wanting to enter
information about a year, make, and model of a vehicle. Will this be a fixed
list from which the user can select? Will the user be allowed to add entries
for a year, make, and model that is not currently in the database?

As to the products. Since you don't yet produce the product, I would guess
the user would have to be able to enter the name of a product, or will this
be a fixed list to which the user may be able to add items?

Will the products be categorized? For example, Engine, Wheels, Body Trim,
etc?

Will the user need the ability to enter freeform text regarding the request?

 

[down in falmes]

The list of vehicle info would be a fixed list. The product info would
actually be fixed as well (we only produce a dozen or so different products),
we just may not produce it for the specific vehicle the customer is calling
about. It would be best to not allow end users to add vehicles or products
but a comment or notes field would be useful. Sorry I did not clarify that
better in the original post.

 

[Klatuu]

Okay, thanks.
You need one table that contains all the year, make, and model combinations.
It also needs a unique primary key. In this case, an Autonumber field would
be a good idea. This is not a properly Normalized design, but since you are
not familiar with Access or Relational Database design, we can keep it
simple. So here is the layout for that:

tblVehicle
VehicleID - Autonumber Primary Key
VehicleYear - Text
VehicleMake - Text
VehicleModel - Text

Now you need a table that describes all your products. It also needs a
primary key

tblProduct
ProductID - Autonumber Primary Key
ProductDescr - Text
(if you need more info on the product, you can add whatever fields you need
here)

Since you don't produce all products for all vehicles, you need a table to
identify which vehicles you do produce the product. This is known as a
Junction table. It is the way to describe which vehicles you make the
product for:

tblVehicleProduct
VehicleID Long Integer
ProductID Long Integer

Make the combination of both of these fields the primary key

Now you need a request field to record requests for products.
tblRequest
RequestID - Autonumber Primary Key
VehicleID - foreign key to tblVehicle (The vehicle the request is for)
ProductID - The Product the request is for
RequestDate - Date/Time (The date and time the request was received
RequestNotes - Memo (freeform text the user can enter about the request)
(you also may want to consider putting info about the requester like name,
address, email, phone, etc.

Start by creating these tables.
Post back when you have that done and we will go on to the next step.
I'm gone for the day.

 

[down in falmes]

Thanks a million! I did the tables as stated.

Okay, thanks.
You need one table that contains all the year, make, and model combinations.
It also needs a unique primary key. In this case, an Autonumber field would
be a good idea. This is not a properly Normalized design, but since you are
not familiar with Access or Relational Database design, we can keep it
simple. So here is the layout for that:

tblVehicle
VehicleID - Autonumber Primary Key
VehicleYear - Text
VehicleMake - Text
VehicleModel - Text

Now you need a table that describes all your products. It also needs a
primary key

tblProduct
ProductID - Autonumber Primary Key
ProductDescr - Text
(if you need more info on the product, you can add whatever fields you need
here)

Since you don't produce all products for all vehicles, you need a table to
identify which vehicles you do produce the product. This is known as a
Junction table. It is the way to describe which vehicles you make the
product for:

tblVehicleProduct
VehicleID Long Integer
ProductID Long Integer

Make the combination of both of these fields the primary key

Now you need a request field to record requests for products.
tblRequest
RequestID - Autonumber Primary Key
VehicleID - foreign key to tblVehicle (The vehicle the request is for)
ProductID - The Product the request is for
RequestDate - Date/Time (The date and time the request was received
RequestNotes - Memo (freeform text the user can enter about the request)
(you also may want to consider putting info about the requester like name,
address, email, phone, etc.

Start by creating these tables.
Post back when you have that done and we will go on to the next step.
I'm gone for the day.

 

[Klatuu]

Okay, now we need to load some data into the tables.
First, create a simple form using tblVehicle as the form's record source.
Put a text box control on the form for each field in the record source. Once
you have selected a record source, you will get a box with a list of all the
fields in the table. Drag each field except VehicleID from the list to the
form.

Now select the first field (VehicleYear) and open the properties dialog.
Name the control txtVehicleYear (That is, the name of the field prefixed
with txt. This is not a requirement, but coding practice and if we use good
names, I will be able to help you more easily and provide examples that will
be meaningful.)

Now we are going to add a little trick that will make entering all this
redundant data a bit easier. A control has a property named Default Value.
This property is used to provide a value when a new record is created so you
don't have to type it in repeatedly. You can, of course, provide a static
value, but for our purposes, we are going to set it up so that is will have
the default value of the record you entered previously. So, for example, you
don't have to type in Chevrolet repeatedly, you will have to only type it in
once. Then when you change to Dodge, you type it in and Dodge will repeat
for each record until you change it again.

This is done using the control's After Update event. Events are "fired"
when specific things happen. The After Update event fires after you have
typed a value into a control and take some action like pressing Tab or Enter
or clicking on another control or navigating to a different record. An After
Update event can be the execution of an expression or a macro or VBA code.
We are going to use VBA code.

With the control txtVehicleYear selected and the properties dialog open,
select the Events tab.
Click the small command button to the right of the box labeled After Update.
A list will pop up. Select Code Builder.
The VBE (Visual Basic Editor) will open with the cursor positioned in the
event procedure. You will see a line that says:
Private Sub txtVehicleYear_AfterUpdate()
Followed by
End Sub

Your code goes between these two lines. This is the code that will cause
the value you enter to be the Default Value for the next record:

Me.txtVehicleYear.DefaultValue = Me.txtVehicleYear

So it will look like this when you are done:

Private Sub txtVehicleYear_AfterUpdate()
Me.txtVehicleYear.DefaultValue = Me.txtVehicleYear
End Sub

(note the code is indented 1 tab from the sub declaration. Indenting makes
the code easier to read. We will cover more of that as we progress)

Now, do the same for all the other controls.

Enter your vehicle information.
We will go to the next step.
Post back if you have problems or questions.

 

[down in falmes]

Awesome! This will take a few days to enter all the data, but I will get back
with you.
Thanks Again!

 

[tony]

VISITA IL MIO SITO WWW.ZAJON.IT
VISIT MY WEBSITE HELO?