How can I table my data to achieve prefilled fields on my final fo

[Guest]

I am working on a new database for '07 and I wanted some of my fields to
prefill for accuracy.
Please, I've heard that the task is simple but I can't find how to go about
it with the resources I have (which are pretty limited!)

This is the information types I am working with:

Each of our customers has an account number and several job numbers-which
are actually just IDs for the locations of each branch of that customer.

For example, customer number 1 has branches number 1, 2, and 3.
Customer number 2 also has 3 branches.
The branches are numbered simply starting from 1 inside each customer's
account.

What I would like to accomplish is, by typing in the account number, the
customer's name automatically prefills in another field. Then I need my form
to recoginze that THIS customer has 3 branches-provide a drop down box from
which I can choose the corresponding branch. Once I choose the corresponding
branch, then the address to that branch will prefill in another field.

I know this has to be possible because I was told that this kind of task was
just barely scratching the surface of Access.

I am running 2003 and again would appreciate any information possible!

Thanks, Crissi

 

[John Vinson]

I am working on a new database for '07 and I wanted some of my fields to
prefill for accuracy.

First off... storing data, such as a date or a year, in a Tablename is
a bad idea. Storing it in a Database name is an even WORSE idea. You
really should consider having an ongoing database with a date field,
or an ID field, or an ItemYear field that can let you select the
information for a given year using a query. This will let you much
more easily select historic data, compare year to year, and so on.

Please, I've heard that the task is simple but I can't find how to go about
it with the resources I have (which are pretty limited!)

This is the information types I am working with:

Each of our customers has an account number and several job numbers-which
are actually just IDs for the locations of each branch of that customer.

For example, customer number 1 has branches number 1, 2, and 3.
Customer number 2 also has 3 branches.
The branches are numbered simply starting from 1 inside each customer's
account.

You then certainly need two tables: a table of Customers, related one
to many to a table of Branches. The Customers table would contain
information relevant to the customer - AccountNumber, CustomerName,
and so on; the Branch table would contain only a CustomerID linking to
the customers table, a BranchNumber (1, 2, 3...), and any information
that you need to record about the branch itself, such as the address.

What I would like to accomplish is, by typing in the account number, the
customer's name automatically prefills in another field. Then I need my form
to recoginze that THIS customer has 3 branches-provide a drop down box from
which I can choose the corresponding branch. Once I choose the corresponding
branch, then the address to that branch will prefill in another field.

You certainly DO NOT want to copy the customer name, or the branch
address into any other table. They should be stored, once and once
only, in their respective table.

You can DISPLAY the customer name in several ways; one simple one
would be to use a Combo Box on your form to select the account number.
Include the customer name in the combo's row source query, and put a
textbox on the form with a control source like

=comboboxname.Column(n)

where n is the zero based index of the CustomerName field in the
combo's query.

You can create a dependent branch combo box by basing it on a Query
which references the account number field. Use a query like

SELECT BranchNumber, BranchAddress
FROM Branches
WHERE Branches.AccountNo = Forms!YourFormName!txtAccountNo
ORDER BY BranchNumber;

This does need one line of VBA code: in the account number combo,
you'll need to Requery the branch combo.

You'll do the same trick with another unbound textbox to display the
address.

In addition - or instead - you could use a Form based on the accounts
table, with a continuous Subform based on the Branches table, to show
all the branches for that customer.

I know this has to be possible because I was told that this kind of task was
just barely scratching the surface of Access.

I am running 2003 and again would appreciate any information possible!

If you could post some more details of your table structure and your
form (or forms), we'll be glad to help.

John W. Vinson[MVP]

 

[John Vinson]

Thank you so much for this information! I have to admit that I'm still
fairly lost. I've had minimal training in Access and don't have the means to
get some more; however, I LOVE the program and the database I built last year
has worked wonders for us, even tho I had to pretty much input everything
manually for each record.

I started building one table so far before I got stumped. I think that in
order for my Queries to run correctly, I have to make sure the foundation is
sound!

I'm a claims administrator for a small hardwood flooring company and when my
boss barks that he needs to know how much money we have paid out in Color
Match Claims this month he wants the information like 2 minutes ago!
I think that my former database fields are GOOD and can be usuable for the
new database; however, I wanted to add the details of the branches like I was
saying.

So far I have a table that consists only of Account Numbers and Customer
Names. I didn't know at this point if I needed to build an individual table
for each customer with their branches. The problem that I run in to is that
within each account, the branches start with 1 and go down the line
numerically. So if I built a "Branch Table" I would end up with duplicates
as branch numbers. Does that make sense? I'm really stumped from the get go
on how to table out the data to get the Queries to include ALL the data from
ALL the tables.

Well... you haven't yet said what "all the tables" are.

You certainly do *NOT* want a separate table for each customer
<shudder>. Your Branch table should have a AccountNumber to link to
the customers table, and a BranchNumber as a separate field; you could
make THESE TWO fields a joint, two field Primary Key, so that you
could have multiple branches for each customer, and multiple records
(for different customers) all with BranchNo = 1.

One suggestion: don't use blanks or special characters in table or
fieldnames. For this part of the application I'd suggest fields like

Customers
AccountNumber <number or text, NOT Autonumber> <primary key>
CompanyName
<if your customers are people not companies use separate LastName
and FirstName fields; if they are companies you might want a separate
Contacts table with people's names, related one to many to the
Customers table by AccountNumber>

Branches
AccountNumber <link to Customers, one field of primary key>
BranchNumber <Integer, second field of primary key>
BranchAddress
<other info about the branch>

I have no idea how your "claims" come into the picture, but if each
Branch can involve multiple Claims then your Claims table might have

Claims
ClaimID <Autonumber Primary Key>
AccountNumber <link to Branches>
BranchNumber <link to Branches>
ClaimDate
<other info about the claim>

John W. Vinson[MVP]

 

[John Vinson]

Okay! I'm starting to see!! So let me ask you this:
In the Branches Table, I would need to enter a new record for each branch?

Of course. The Branches table is a collection of records, each record
describing a Branch. One branch - one record.

So that it would look like so:

AccountNumber BranchNumber BranchAddy
11112 1 somewhereville
11112 2 somewhere-elseville
Exactly.

I'm sorry my peabrain doesn't comprehend so quickly, and I really really
appreciate your help on this! I'm very excited at the possibilities I can
work with Access!!!

You can (once you get the table structure right!) create a Form based
on the Accounts table, with a Subform based on the Branches table; you
can even have it automatically calculate the next sequential
branchnumber for that account.

John W. Vinson[MVP]