report of 2 tables

  • Thread starter Thread starter Liat
  • Start date Start date
L

Liat

Hello,

I have a main table, payments table and estimations table.
(The ID of the main table equals suitID that exists in
both payments and estimations tables)

I would like to create a report that gives, for example,
all the estimations and payments for certain date.

How can I make this query on 2 different tables?

I tried to create a query using design view , but it
isn't good...

Thanks a lot,
Liat
 
Hi Liat.
Open a new query in design view,
add the two or three tables you wish to integrate, and
create the link between the suiteID of the tables.
Now simply dbl click the data elements in the table
windows to add the desired data elements to displayed
fields.
To set the selection possibility for a specific data,
type "[Enter Date to be Printed]" in the criteria field
under the date column.
This should give you the desired data.
You now can create a report using this query as your data
source, and in the report you can summarize the data as
you wish.
Hope this gets you going.
Fons
 
Hi Fons,

Thank you very much for your answer.

I already tried this but the problem is that for example,
I have for record number one of the main table, 3
estimations and 2 payments. For record number 2, I have
only one estimation.

The result of this query is: 3 lines of all the
estimations of the first record with payment one, 3 lines
of all the estimations of the first record with payment
two and one line of the second record with the estimation.

This twists all the information I need from those tables.

I need to know for each record of the main table, what is
the total estimations and what is the total payments.
When it appears several times, I can't do it...

What did I do wrong?

Thanks a lot,
Liat

table and estimations table.

-----Original Message-----
Hi Liat.
Open a new query in design view,
add the two or three tables you wish to integrate, and
create the link between the suiteID of the tables.
Now simply dbl click the data elements in the table
windows to add the desired data elements to displayed
fields.
To set the selection possibility for a specific data,
type "[Enter Date to be Printed]" in the criteria field
under the date column.
This should give you the desired data.
You now can create a report using this query as your data
source, and in the report you can summarize the data as
you wish.
Hope this gets you going.
Fons
-----Original Message-----
Hello,

I have a main table, payments table and estimations table.
(The ID of the main table equals suitID that exists in
both payments and estimations tables)

I would like to create a report that gives, for example,
all the estimations and payments for certain date.

How can I make this query on 2 different tables?

I tried to create a query using design view , but it
isn't good...

Thanks a lot,
Liat
.
Click to expand...
.
Click to expand...
 
Hi Liat,
In that case I would take a different approach,
I would create one report for the SuiteID and than create
two subreports one for the estimates and one for payments.
You can summarize the reports independently.
Hope this helps.
Fons
-----Original Message-----
Hi Fons,

Thank you very much for your answer.

I already tried this but the problem is that for example,
I have for record number one of the main table, 3
estimations and 2 payments. For record number 2, I have
only one estimation.

The result of this query is: 3 lines of all the
estimations of the first record with payment one, 3 lines
of all the estimations of the first record with payment
two and one line of the second record with the estimation.

This twists all the information I need from those tables.

I need to know for each record of the main table, what is
the total estimations and what is the total payments.
When it appears several times, I can't do it...

What did I do wrong?

Thanks a lot,
Liat

table and estimations table.

-----Original Message-----
Hi Liat.
Open a new query in design view,
add the two or three tables you wish to integrate, and
create the link between the suiteID of the tables.
Now simply dbl click the data elements in the table
windows to add the desired data elements to displayed
fields.
To set the selection possibility for a specific data,
type "[Enter Date to be Printed]" in the criteria field
under the date column.
This should give you the desired data.
You now can create a report using this query as your data
source, and in the report you can summarize the data as
you wish.
Hope this gets you going.
Fons
-----Original Message-----
Hello,

I have a main table, payments table and estimations table.
(The ID of the main table equals suitID that exists in
both payments and estimations tables)

I would like to create a report that gives, for example,
all the estimations and payments for certain date.

How can I make this query on 2 different tables?

I tried to create a query using design view , but it
isn't good...

Thanks a lot,
Liat
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Thank you very very much, it works now!!!

If I need the sum total of all the records for both
estimations and payments, how can I do that? Should it be
in the main report?

-----Original Message-----
Hi Liat,
In that case I would take a different approach,
I would create one report for the SuiteID and than create
two subreports one for the estimates and one for payments.
You can summarize the reports independently.
Hope this helps.
Fons
-----Original Message-----
Hi Fons,

Thank you very much for your answer.

I already tried this but the problem is that for example,
I have for record number one of the main table, 3
estimations and 2 payments. For record number 2, I have
only one estimation.

The result of this query is: 3 lines of all the
estimations of the first record with payment one, 3 lines
of all the estimations of the first record with payment
two and one line of the second record with the estimation.

This twists all the information I need from those tables.

I need to know for each record of the main table, what is
the total estimations and what is the total payments.
When it appears several times, I can't do it...

What did I do wrong?

Thanks a lot,
Liat

table and estimations table.

-----Original Message-----
Hi Liat.
Open a new query in design view,
add the two or three tables you wish to integrate, and
create the link between the suiteID of the tables.
Now simply dbl click the data elements in the table
windows to add the desired data elements to displayed
fields.
To set the selection possibility for a specific data,
type "[Enter Date to be Printed]" in the criteria field
under the date column.
This should give you the desired data.
You now can create a report using this query as your data
source, and in the report you can summarize the data as
you wish.
Hope this gets you going.
Fons
-----Original Message-----
Hello,

I have a main table, payments table and estimations table.
(The ID of the main table equals suitID that exists in
both payments and estimations tables)

I would like to create a report that gives, for example,
all the estimations and payments for certain date.

How can I make this query on 2 different tables?

I tried to create a query using design view , but it
isn't good...

Thanks a lot,
Liat
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
To summarize do the following:
You can use a text box in each subreport to calculate the
totals. The text box expression would be something like:
=Sum(costfield)
Then the main report totals text box can refer to the
subreport values with this kind of expression:
=subreport1.Report.total1 - subreport2.Report.total2
Hope this solves it for you.
Fons
-----Original Message-----
Thank you very very much, it works now!!!

If I need the sum total of all the records for both
estimations and payments, how can I do that? Should it be
in the main report?

-----Original Message-----
Hi Liat,
In that case I would take a different approach,
I would create one report for the SuiteID and than create
two subreports one for the estimates and one for payments.
You can summarize the reports independently.
Hope this helps.
Fons
-----Original Message-----
Hi Fons,

Thank you very much for your answer.

I already tried this but the problem is that for example,
I have for record number one of the main table, 3
estimations and 2 payments. For record number 2, I have
only one estimation.

The result of this query is: 3 lines of all the
estimations of the first record with payment one, 3 lines
of all the estimations of the first record with payment
two and one line of the second record with the estimation.

This twists all the information I need from those tables.

I need to know for each record of the main table, what is
the total estimations and what is the total payments.
When it appears several times, I can't do it...

What did I do wrong?

Thanks a lot,
Liat

table and estimations table.


-----Original Message-----
Hi Liat.
Open a new query in design view,
add the two or three tables you wish to integrate, and
create the link between the suiteID of the tables.
Now simply dbl click the data elements in the table
windows to add the desired data elements to displayed
fields.
To set the selection possibility for a specific data,
type "[Enter Date to be Printed]" in the criteria field
under the date column.
This should give you the desired data.
You now can create a report using this query as your
data
source, and in the report you can summarize the data as
you wish.
Hope this gets you going.
Fons
-----Original Message-----
Hello,

I have a main table, payments table and estimations
table.
(The ID of the main table equals suitID that exists in
both payments and estimations tables)

I would like to create a report that gives, for
example,
all the estimations and payments for certain date.

How can I make this query on 2 different tables?

I tried to create a query using design view , but it
isn't good...

Thanks a lot,
Liat
.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Hi Fons,

I tried to do so, but when I open the report it asks to
enter parameter value for the subform name...
I used the expression builder and chose the report and
the field from the list.

What can I do?

Thank you very much for all of your help,
Liat

-----Original Message-----
To summarize do the following:
You can use a text box in each subreport to calculate the
totals. The text box expression would be something like:
=Sum(costfield)
Then the main report totals text box can refer to the
subreport values with this kind of expression:
=subreport1.Report.total1 - subreport2.Report.total2
Hope this solves it for you.
Fons
-----Original Message-----
Thank you very very much, it works now!!!

If I need the sum total of all the records for both
estimations and payments, how can I do that? Should it be
in the main report?

-----Original Message-----
Hi Liat,
In that case I would take a different approach,
I would create one report for the SuiteID and than create
two subreports one for the estimates and one for payments.
You can summarize the reports independently.
Hope this helps.
Fons
-----Original Message-----
Hi Fons,

Thank you very much for your answer.

I already tried this but the problem is that for example,
I have for record number one of the main table, 3
estimations and 2 payments. For record number 2, I have
only one estimation.

The result of this query is: 3 lines of all the
estimations of the first record with payment one, 3 lines
of all the estimations of the first record with payment
two and one line of the second record with the estimation.

This twists all the information I need from those tables.

I need to know for each record of the main table,
Click to expand...
what
is
the total estimations and what is the total payments.
When it appears several times, I can't do it...

What did I do wrong?

Thanks a lot,
Liat

table and estimations table.


-----Original Message-----
Hi Liat.
Open a new query in design view,
add the two or three tables you wish to integrate, and
create the link between the suiteID of the tables.
Now simply dbl click the data elements in the table
windows to add the desired data elements to displayed
fields.
To set the selection possibility for a specific data,
type "[Enter Date to be Printed]" in the criteria field
under the date column.
This should give you the desired data.
You now can create a report using this query as your
data
source, and in the report you can summarize the data as
you wish.
Hope this gets you going.
Fons
-----Original Message-----
Hello,

I have a main table, payments table and estimations
table.
(The ID of the main table equals suitID that exists in
both payments and estimations tables)

I would like to create a report that gives, for
example,
all the estimations and payments for certain date.

How can I make this query on 2 different tables?

I tried to create a query using design view , but it
isn't good...

Thanks a lot,
Liat
.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Hi Liat
Would you give me the names of the reports and the names
of the textboxes on your subreports containing the sum or
the formula used on the subreports.
With other words give me some specifics.
Other wise, if you wish you may send me your mdb file at
my name fonsponsio and at @sbcglobal.net, I'll gladly
look at it. In order to do so just remove the mdb
extnsion from the file name so it does not get stripped by
virus programs throught the email system.
Fons
-----Original Message-----
Hi Fons,

I tried to do so, but when I open the report it asks to
enter parameter value for the subform name...
I used the expression builder and chose the report and
the field from the list.

What can I do?

Thank you very much for all of your help,
Liat

-----Original Message-----
To summarize do the following:
You can use a text box in each subreport to calculate the
totals. The text box expression would be something like:
=Sum(costfield)
Then the main report totals text box can refer to the
subreport values with this kind of expression:
=subreport1.Report.total1 - subreport2.Report.total2
Hope this solves it for you.
Fons
-----Original Message-----
Thank you very very much, it works now!!!

If I need the sum total of all the records for both
estimations and payments, how can I do that? Should it be
in the main report?


-----Original Message-----
Hi Liat,
In that case I would take a different approach,
I would create one report for the SuiteID and than
create
two subreports one for the estimates and one for
payments.
You can summarize the reports independently.
Hope this helps.
Fons
-----Original Message-----
Hi Fons,

Thank you very much for your answer.

I already tried this but the problem is that for
example,
I have for record number one of the main table, 3
estimations and 2 payments. For record number 2, I have
only one estimation.

The result of this query is: 3 lines of all the
estimations of the first record with payment one, 3
lines
of all the estimations of the first record with payment
two and one line of the second record with the
estimation.

This twists all the information I need from those
tables.

I need to know for each record of the main table, what
is
the total estimations and what is the total payments.
When it appears several times, I can't do it...

What did I do wrong?

Thanks a lot,
Liat

table and estimations table.


-----Original Message-----
Hi Liat.
Open a new query in design view,
add the two or three tables you wish to integrate, and
create the link between the suiteID of the tables.
Now simply dbl click the data elements in the table
windows to add the desired data elements to displayed
fields.
To set the selection possibility for a specific data,
type "[Enter Date to be Printed]" in the criteria
field
under the date column.
This should give you the desired data.
You now can create a report using this query as your
data
source, and in the report you can summarize the data
as
you wish.
Hope this gets you going.
Fons
-----Original Message-----
Hello,

I have a main table, payments table and estimations
table.
(The ID of the main table equals suitID that exists
in
both payments and estimations tables)

I would like to create a report that gives, for
example,
all the estimations and payments for certain date.

How can I make this query on 2 different tables?

I tried to create a query using design view , but it
isn't good...

Thanks a lot,
Liat
.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top