|
 |
access thread: MS-ACCESS + sql (I am sure u have all done this before)
Message #1 by avivit@m... on Wed, 5 Sep 2001 13:54:44
|
|
I have table that holds records with the following fields: name,
houseSize, dateOfBuying.
Now, each name can be in more than one record, if it has more than 1 house
(could be 3 or even 4).
I need to have a form that holds one record for each name, and there I'd
like to see each houseSize,
and dateOfBuying for that house. All in 1 record of course.
(Later I would like to calculate things like the time thaat passed from
buying the first house until the last,
and also calculate the average of the houseSize for each name. And all in
the same record.
I can o this in excel if not possible in access. But the first part (all
but calculations) must work in access).
How can I do that???
After goinf one step ahead, there will still be a probelm of not knowing
in advance the number
of houses each has. right?)
Anyway. just help.
Thanks
Message #2 by "Rogers, Robert" <rrogers@b...> on Wed, 5 Sep 2001 09:01:02 -0400
|
|
I would try using a subform. First you must create a one to many
relationship.
-----Original Message-----
From: avivit@m... [mailto:avivit@m...]
Sent: Wednesday, September 05, 2001 9:55 AM
To: Access
Subject: [access] MS-ACCESS + sql (I am sure u have all done this
before)
I have table that holds records with the following fields: name,
houseSize, dateOfBuying.
Now, each name can be in more than one record, if it has more than 1 house
(could be 3 or even 4).
I need to have a form that holds one record for each name, and there I'd
like to see each houseSize,
and dateOfBuying for that house. All in 1 record of course.
(Later I would like to calculate things like the time thaat passed from
buying the first house until the last,
and also calculate the average of the houseSize for each name. And all in
the same record.
I can o this in excel if not possible in access. But the first part (all
but calculations) must work in access).
How can I do that???
After goinf one step ahead, there will still be a probelm of not knowing
in advance the number
of houses each has. right?)
Anyway. just help.
Thanks
Message #3 by Walt Morgan <wmorgan@s...> on Wed, 05 Sep 2001 08:07:34 -0500
|
|
You may wish to consider two tables; one to hold the name of the owner and
their contact information and one to hold their transactions(houses bought).
By doing this you create an infinitely more flexible solution. You have
correctly recognized the problem with attempting to put an indeterminate
number of transactions into a single record.
This solution will pave the way for the type of information you indicated
would be asked of the system.
Walt
Message #4 by "Pardee, Roy E" <roy.e.pardee@l...> on Wed, 05 Sep 2001 08:13:59 -0700
|
|
Sounds like you need a crosstab query--try the wizard and see the help file
for details.
HTH,
-Roy
-----Original Message-----
From: avivit@m... [mailto:avivit@m...]
Sent: Wednesday, September 05, 2001 6:54 AM
To: Access
Subject: [access] MS-ACCESS + sql (I am sure u have all done this
before)
I have table that holds records with the following fields: name,
houseSize, dateOfBuying.
Now, each name can be in more than one record, if it has more than 1 house
(could be 3 or even 4).
I need to have a form that holds one record for each name, and there I'd
like to see each houseSize,
and dateOfBuying for that house. All in 1 record of course.
(Later I would like to calculate things like the time thaat passed from
buying the first house until the last,
and also calculate the average of the houseSize for each name. And all in
the same record.
I can o this in excel if not possible in access. But the first part (all
but calculations) must work in access).
How can I do that???
After goinf one step ahead, there will still be a probelm of not knowing
in advance the number
of houses each has. right?)
Anyway. just help.
Thanks
|
|
|
View Cart
