Query-design

[Guest]

I am newbie….using access 2000, no VB experience, just able to use some
simple functions in query design.
3 tables:
Projects (key is unique number)
People (key is persons name, there are no repeats)
Accounts (key is account number)

I want to run a query so that it associates an account number with each
project. However, each person in the “peoples†table can have
multipleâ€accounts numbers†in the accounts table. When I run the query, I
get multiple rows of the same project with as many accounts as there are in
the accounts table for that one person in the peoples table (for example. 3
accounts numbers produces 3 rows with the same person names for the same
project). I have tried playing around by changing the relationships but to no
avail. Can anyone help me out?
Thanks in advnace

 

[Steve]

Star with a check of the design of your tables. Do they look like this --
TblPerson
PersonID
FirstName
MiddleInitial
LastName
<<Othe person fields you need for database>>

TblAccount
AccountID
AccountNumber
AccountDescription
<<Other account fields you need for database>>

TblProject
ProjectID
ProjectDescription
StartDate
FinishDate
AccountID
PersonID

If more than one person can be assigned to a project you need a table like
this:
TblProjectPerson
ProjectPersonID
ProjectID
PersonID

and no PersonID in TblProject.

If a project can be assigned multiple accounts you need a table like this:
TblProjectAccount
ProjectAccountID
ProjectID
AccountID

and no AccountID in TblProject.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Not really, I thought I could just select the unique fields and set them as
primary key and establish the relationships.
I see that you have a unique id in each table and that in the main projects
table you have the other two id's. What goes data goes into each of those
fields as i fill the table? I usually use the auto number field to creat
unique ID.
The last two table sets you describe, are they in addition to the other
tables or in place of them.
thanks......

 

[Steve]

Answers in line ---

Not really, I thought I could just select the unique fields and set them
as
primary key and establish the relationships.
I see that you have a unique id in each table and that in the main
projects
table you have the other two id's. What goes data goes into each of those
fields as i fill the table?

AccountID will be a unique number from 1 to whatever that uniquely
identifies each account. On your project form, you will have a combobox for
selecting the Account the project is assigned. The combobox will display
account number and description and the combobox will have the value of
AccountID when you make your selection. When the project record is saved to
TblProject, AccountID will be saved to the AccountID field.

PersonID will be a unique number from 1 to whatever that uniquely identifies
each person. On your project form, you will have a combobox for selecting
the person assigned to the project. The combobox will display the person's
name and the combobox will have the value of PersonID when you make your
selection. When the project record is saved to TblProject, PersonID will be
saved to the PersonID field.

I usually use the auto number field to creat
unique ID.
The last two table sets you describe, are they in addition to the other
tables or in place of them.

If more than one person can be assigned to a project you need
TblProjectPerson IN ADDITION TO the other tables.

If a project can be assigned multiple accounts you need TblProjectAccount
IN ADDITION TO the other tables.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)