By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

relating queries to tables

P: n/a
It is possible to relate queries to tables, right? It seems logical
but when I try to match my queries to any of the tables or even to
each other it gives me a blank relationship. What could I possibly be
doing wrong on this?

Thanks for the help,
Stephen
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Are you working in the Relationships window? If so, this is where you will
set links between tables on common fields so that the data in each table can
be related to the other table. You can also enforce "Referential Integrity"
here to prevent removal of records that have records in the associated
tables, auto-delete associated records, and/or auto-update associated
records.

Now, when you make your queries based on these tables, these links will
automatically be shown in the query design grid. You can adjust them here if
necessary for that query (it doesn't change what's been set in the
Relationships window). If you place a query and a table into a new query,
you can create a link at this point by dragging the associated field from
one of them to the other.

There are different types of links. The first will link the two tables or
queries where both sides of the link have equal values in their fields.
These are the only values that will be returned by the query with this type
of link. The other two will return all records from one side or the other
and only those records from the opposite side where the field values match.

If none of this describes what you are trying to do, I'll need a more
precise description of what you're trying to do.

--
Wayne Morgan
MS Access MVP
"Stephen" <st*********@yahoo.com> wrote in message
news:4c**************************@posting.google.c om...
It is possible to relate queries to tables, right? It seems logical
but when I try to match my queries to any of the tables or even to
each other it gives me a blank relationship. What could I possibly be
doing wrong on this?

Thanks for the help,
Stephen

Nov 12 '05 #2

P: n/a
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<Pi*********************@newssvr11.news.prodi gy.com>...
Are you working in the Relationships window? If so, this is where you will
set links between tables on common fields so that the data in each table can
be related to the other table. You can also enforce "Referential Integrity"
here to prevent removal of records that have records in the associated
tables, auto-delete associated records, and/or auto-update associated
records.

Now, when you make your queries based on these tables, these links will
automatically be shown in the query design grid. You can adjust them here if
necessary for that query (it doesn't change what's been set in the
Relationships window). If you place a query and a table into a new query,
you can create a link at this point by dragging the associated field from
one of them to the other.

There are different types of links. The first will link the two tables or
queries where both sides of the link have equal values in their fields.
These are the only values that will be returned by the query with this type
of link. The other two will return all records from one side or the other
and only those records from the opposite side where the field values match.

If none of this describes what you are trying to do, I'll need a more
precise description of what you're trying to do.

--


[This is on behalf of my friend who posed this question]

Mr. Morgan,

S Burks inquired about database relationships on my behalf.
Unfortunately, my description of the problem was not enough so that your
assistance could solve my problem. I work as a real estate consultant and one
of the tasks I was hoping to accomplish was to establish a database that would
help us organize and track the apartment market the Atlanta. I think I was
successful in getting the preliminary work done, however, when trying to
create a useful report framework I have ran into trouble.

I have created a primary property data table which is related to a unit mix
table as one-to-many. This relationship is successful in displaying the unit
mix data as it relates to the general property data when I am working with the
tables. However, queries on the unit mix were done in order to calculated
summary data. The relationship between the general property data and the
queries could not be established, causing the repeat of information.

In the final report framework I would like to be able to display the unit mix
information (from queries) as it relates to each property (table). I have not
been able to relate the queries to the general property data in such as way as
to do this.

So, what I am interested in figuring out is how to create the proper
relationship links so that on the property fact sheet form I can embed the
unit mix summary and unit mix weighted average information pertaining to that
project only.

Thank you for your help.

Darice
Nov 12 '05 #3

P: n/a
I don't fully follow what you are doing. I'm guessing Properties are
apartment complexes and the related data are available apartments.

You can include queries in a new query as if they are tables. You can also
create links between these queries or between them and any tables you've
also added to the new query. This is done in the query design grid by
dragging and dropping the associated fields between them.

In a form or report, you may want to consider a subform or subreport. The
main report would have the Properties and the subreport would contain the
associated data. The 2 reports would be linked by a common field.

In a report, you can also do "grouping", creating a header for the Property
then having related details in the detail section.

Property1
detail 1
detail 2

Property2
detail 1
detail 2

Also, you may want to check the Unique Records and Unique Values properties
of the queries. This can be set by right clicking the background in the
query design grid in the section where the tables are added and choosing
Properties.

--
Wayne Morgan
Microsoft Access MVP
"Stephen" <st*********@yahoo.com> wrote in message
news:4c**************************@posting.google.c om...
[This is on behalf of my friend who posed this question]

Mr. Morgan,

S Burks inquired about database relationships on my behalf.
Unfortunately, my description of the problem was not enough so that your
assistance could solve my problem. I work as a real estate consultant and one of the tasks I was hoping to accomplish was to establish a database that would help us organize and track the apartment market the Atlanta. I think I was successful in getting the preliminary work done, however, when trying to
create a useful report framework I have ran into trouble.

I have created a primary property data table which is related to a unit mix table as one-to-many. This relationship is successful in displaying the unit mix data as it relates to the general property data when I am working with the tables. However, queries on the unit mix were done in order to calculated
summary data. The relationship between the general property data and the
queries could not be established, causing the repeat of information.

In the final report framework I would like to be able to display the unit mix information (from queries) as it relates to each property (table). I have not been able to relate the queries to the general property data in such as way as to do this.

So, what I am interested in figuring out is how to create the proper
relationship links so that on the property fact sheet form I can embed the
unit mix summary and unit mix weighted average information pertaining to that project only.

Thank you for your help.

Darice

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.