Responses and additional questions inline.
Sorry for the wording of my first post...I'm not looking to hire anyone...I
just want guidance.
No problem. Thought maybe you weren't familiar with the forum.
I don't know how to refer to more than one table in a form.
Short answer is that you would use a query. Tables are for storing data.
Queries are for sorting and otherwise arranging and organizing it.
I have a user, PhoneNumber, Charges, fund, groups, and restriction table.
These are six separate tables? If so, how are they related?
PhoneNumber key is phone number
The phone number itself is the primary key?
If you mean that the user name is the primary key (PK) that could become a
problem. A PK must be unique.
If a new user takes over a number...that will not effect past charges in the
charge table which has the old PhoneNumber/User combination. The PhoneNumber
table contains the most up to date information about that number. The charges
table contains monthly charges and the phone/person associated with the
charge. These tables are separated so our data entry does not have to enter
redundant info...and we can run reports on phone attributes.
A good general rule for table design is that you should be able to describe
the table's function in a single sentence without using the word "and".
Charges and the associated person do not belong in the same table (unless
maybe each charge is associated with a different person, and no two charges
are associated with the same person).
Because people can have more than one phone the relationship
PhoneNumber[User] to User[User] is Many to One.
Then the phone number table (tblPhoneNumber) needs (in addition to its own
PK) a foreign key (FK) to correspond to the PK from the user table (tblUser).
A foreign key is a field that is placed in the table during the design
stage. While a PK is specified in table design, a FK is established by its
relationship to the PK from the "parent" table. The FK must be of the same
data type as the PK, or of data type Number if the PK is Autonumber. You may
already know this, or maybe it is already done, but you identified yourself
as a beginner and did not provide specifics of the relationships. If I am
telling you what you already know, please understand the reason.
There is also a PhoneNumber[PhoneNumber] to Charge[PhoneNumber] relations
ship of One to Many.
Sounds good.
There is a User[User] to Charge[User] to Charge[User] relationship of One to
Many...but I'm not sure if I should change it to PhoneNumber[User] to
Charge[User]?
The tables were populated with enough information to test with. What would
be really helpful is if I could get my hands on a sample db with something
like this that I could take apart to see how it works...Northwind has nothing.
Let me suggest something like the following (tblSomething refers to a table):
tblUser
UserID (PK - could be autonumber, or account number, etc.)
FirstName
LastName
Other personal information
tblPhoneNumber
NumberID (PK - could be the phone number, but it would be best in any case
if it is a field that cannot be edited)
UserID (FK)
PhoneNumber (if phone number is not the PK)
Other information specific to the phone number
tblCharges
ChargeID (PK)
NumberID (FK)
Date
Amount
Other details specific to the charge
In the relationships window (Tools > Relationships) establish a relationship
between each PK and the FK with the same name (FK does not need to be the
same name, but I find it is easier to keep track of when it is the same).
Click Enforce Relational Integrity.
Base a form on each of these tables. Set the default view for frmCharges to
Continuous. In form design view drag the icon for frmCharges (the form based
on tblCharges) onto frmPhoneNumber. Similarly, drag the icon for frmPhone
onto frmUser. Experiment with some data entry. If this seems to be heading
in the right direction we can work on the details. For instance, if the
phone number assigned to each User is drawn from an existing collection of
phone numbers then a combo box could be used to select the phone number. If
the number is not likely to change often then some other means could be found
of associating it with the user.
One more point: At any point after the table design is complete you can
base a query on a table. This will allow you to sort users alphabetically,
combine FristName and LastName, select a date range, and so forth. Forms and
reports can then be based on these queries rather than on the tables.
BruceM said:
The point of the forum is for users to exchange information and to learn from
that exchange. People who post here are volunteers. Sometimes somebody will
offer to take a look at the database after an exchange of messages doesn't
produce results, but the public forum is the place to begin. You may need to
post a few more details, I'm going to go ahead and suggest you may want to
rethink the database design. If each user could have more than one phone
number there should probably be a User table, a Phone Number table, and a
Charges table. If each user has only one phone number and each phone number
is associated with only one user you can probably continue to use two tables.
It seems you are storing data redundantly if you are storing user
information with each individual charge. The point of relational databases
is that you store things like user information once only, and thereafter
refer to that user's information rather than copying it to another table. If
a user's name changes, or if a different user is associated with a particular
phone number, records from before the change may not be accurate.
Do you have table relationships set up? Click Tools > Relationships to see
if anything shows up. Does the database already contain records? This could
affect your choices, but trying to patch a clumsy database design is often
frustrating and ultimately futile. Does your phone number table contain a
primary key? If phone number is the primary key, what happens if another
user takes over that phone number? If you change the user record to reflect
the new name, that person may end up associated with all past activity for
that phone number.
To your original question you could maybe design a query to produce the
relevant information that needs to appear on the form, then use a combo box
to based on the query to populate text boxes on the form. But if you are new
to Access, I urge you to step back for a good look at what you are working
with. What you are attempting is probably pretty straightforward, but cannot
be addressed in a few short responses.
:
I'm new to Access and databases...the person who normally does this was let
go...and I need to create a form that verifies the user when you enter a
phone number into it. Please help!
Table 1 [PhoneNumber]
Fields = PhoneNumber, User, and other related fields.
Table 2 [Charges]
Fields = ChargeID (autonumber), PhoneNumber, User, Monthly, Voice,
Detail, Direct, Other, Date, Fund
How do I create a form that grabs the User from the PhoneNumber Table so the
data entry person knows he/she is inserting the charges for the correct
person? The Charges will populate the Charges table.
I've looked all over for help with this...one guy even wanted to charge us
$5000 for a single form. If you can help but need more information...we can
continue via email. Thanks.
