Change table structure?

[JSchumacher]

Each user in the database is assigned an area of the country and a section of the alphabet and work files that match those criteria.

The main table, tblData, has fields for Name and Branch. I have a seperate table, tblBranch that lists the state that branch is in, and a table, tblStates, listing each state and the "Area" it is in.

tblUsers has the following fields:
Area
AlphaSortStart
AlphaSortEnd
Login

When I start with the User, I am able to pull up all records that match their Area and Alpha Sort (matching the Name field in tblData) and they can work the accounts. What I need to do now is create reports based on the main table. The reports will need to be broken down by Area and User. Now I'm not sure that I've set up the tables in the best way.

I've thought of assigning the User Login to each record as it's created/imported, but that dosen't leave any room for changes - either with our User turn around, or changing the Alpha Sorts.

I'm looking for either a better way of structuring the tables or a way of reporting on what I currently have set up.

Thanks,

Judie

 

[montrose]

JSchumacher, Might be able to help if we had a better picture of your table structure. How are these tables related? Do you have primary/foreign keys for each table? A bit more explanation, please. Thanks, Montrose Learn what you can and share what you know.

 

[JSchumacher]

I don't know what a Foreign Key is, but each table has a primary key as follows:

tblData - AccountNumber
tblBranch - BranchNumber
tblState - State
tblUser - Login

tblData relates to tblBranch by BranchNumber, tblBranch to tblState by State, and tblState to tblUser by Area. There are multiple records in tblUser with the same Area. Right now each Area has 3 Users, but that could change, as could the Alpha splits. Also, each area may have a different split.

Perhaps what I need to do is create a new table to relate to tblState by area and tblUser by the split so that there aren't multiple records referenced. I could have tblSplit with fields Area, AlphaStart, AlphaEnd, and an AutoNumber. The AutoNumber would relate to a new field in tblUser.

Your thoughts??

 

[montrose]

JSchumacher,
Forgive me if you know some of this already, but I don't want to assume what you know or don't know. First suggestion is to go into Access Help, type in "relationships", click on "create or modify relationships", and go through the choices to familiarize yourself a little. Since Access is a 'realtional' db tool, it's pretty important to have a general understanding of how your tables will tie together so you can get information put in and get the information out when you want it. Access comes with some example databases that can be explored. It will give you a basic idea of a primary key and a foreign key. Also check out the FAQS written for this forum and all the other MS Access forums.

Some basic questions to help you sort this out. Your current tables:
tblData - AccountNumber
tblBranch - BranchNumber
tblState - State
tblUser - Login

1. One User is responsible for one or many Branches?
2. More than one User is responsible for one or many Branches?
3. A Branch is in one State?
4. One State can have many Branches?
4. One User is responsible for one State or many States?
5. More than one User is responsible for one or many States?

Is Data (your Account Number field) related to a State? a Branch? more than one State? more than one Branch?
You said, "There are multiple records in tblUser with the same Area. Right now each Area has 3 Users, but that could change, as could the Alpha splits. Also, each area may have a different split." What is an 'Area' (Branch, State, something else?)? What is an 'Alpha' split?

The baisc idea here is to make sure you've 'defined' all the basic groupings (tables) of information you need. Then to make sure you've accounted for everything that encompasses or goes along with each of those groupings (the fields you need). You're trying to get it broken down into truly unique pieces of information and have the least amount of redundant data. Sometimes that will fit into one table, and sometime that will fit into multiple tables that are related to each other. You put it all back together again by asking questions about your data (queries), providing a way for for the user to input info (your forms), and then present all this in a way that makes sense (reports based on your queries).

Try playing around with all this on paper first if necessary. You're the one who knows this best but we can help you get it together in an efficient and logical way! Good to bounce ideas off others as you're doing here in the forum. Post back with your design plans and we can go from there. Good luck! HTH, Montrose



Learn what you can and share what you know.

 

[JSchumacher]

First of all Montrose, thanks for your help so far. It's really helping me focus my thoughts. I'm still stuck, but let me see if I can explain it further:

One Branch can have many accounts
One State can have many Branches
One Area can have many States (The office is divided into 4 different "Areas", each assigned certain states)

Here is where I'm still struggling.
Many Users will have one Area, but the Accounts they will have are determined by the LastName of the Account holder.
Ex:
Area 1 -
User1 A-G
User2 H-N
User3 O-Z

Area 2 -
User4 A-Hm
User5 Hn-Ml
User6 Mm-Z

I need to allow for additional Users in each area, and for the Alpha Splits to be different in each area and to change over time.

I could assign each new record in tblData the correct UserID from tblUser and have tblUser relate to tblData. However that would require updating tblData anytime a State is reassigned to a different Area, or the Alpha Splits are changed.

I think there's a better solution, but I can't get my arms around it. Any help would be appreciated.

 

[montrose]

JSchumacher, Wondered if you got this figured out. Didn't mean to abandon you, both computers in my home office bonked out on me on 01-15-02! Finally got one replaced and have spent hours catching up on my 'reading' here on Tek-Tips. Let me know if you want to bounce ideas around. Montrose. Learn what you can and share what you know.

 

[domt]

I want to enter data from an Access file to a Listbox in a VB6 program. But first I want to resort the Access file.
What VB command(s) do I use to do this?
In other words, what VB command accesses the Access file's Sort headings.
I am a rank beginner in this and any help would be appreciated.

 

[JSchumacher]

Montrose,

Sorry to hear about your computers. I hope things are getting back on track for you. I wasn't sucessful in finding a better design for the tables, but I was able to solve my problem in query design. Any query I use requires the first two letters of the name field to be between the alphastart and alphaend. It seems to be working.

Thanks for your input. It helped me to stop and think out (and draw out) my design. That will be valuable as I continue to design reports and queries for this application.

 

[lespaul]

JSchumacher,

you may want to create a new table for alphasplits.

SplitName Beginning Ending
Split1 A G
Split2 H R
etc

this would allow easy changes of the alpha splits and allow you to see which account holders belong to each user by searching for account holder by whether or not the name is between the beginning and ending in alphasplit.

 

[Guest_imported]

I have 2 tables where I have to fetch data from one table based on the other.Its a simple query.But the problem is the table which i compare the data of a column has multiple values separated bt commas.how do i compare multiple values of a column in a query