473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How can I create this type of report?

I have a 2 unmatched queries
Table1_view (records in table1 not in table2)
Table2_view (records in table2 not in table1)

Both table contain fields
ReportID
ReportName
ReportOwner
In_Table1 (True or False)
In_Table2 (True or False)

I need to create a single report that has all 5 fields no matter what
query it comes from
Group by ReportOwner
Show ReportID, ReportName and (If In_Table1 or In_Table2 is true show
a green box or
If In_Table1 or In_Table2 is false show a red box)

Any guidance or suggestions? Is this possible
Oct 30 '08 #1
6 1592
On Thu, 30 Oct 2008 04:13:27 -0700 (PDT), Steve <da********@yahoo.com>
wrote:

Create a union query of the two queries. Then base your report on it.

-Tom.
Microsoft Access MVP

>I have a 2 unmatched queries
Table1_view (records in table1 not in table2)
Table2_view (records in table2 not in table1)

Both table contain fields
ReportID
ReportName
ReportOwner
In_Table1 (True or False)
In_Table2 (True or False)

I need to create a single report that has all 5 fields no matter what
query it comes from
Group by ReportOwner
Show ReportID, ReportName and (If In_Table1 or In_Table2 is true show
a green box or
If In_Table1 or In_Table2 is false show a red box)

Any guidance or suggestions? Is this possible
Oct 30 '08 #2
On Oct 30, 8:32*am, Tom van Stiphout <tom7744.no.s...@cox.netwrote:
On Thu, 30 Oct 2008 04:13:27 -0700 (PDT), Steve <dafella...@yahoo.com>
wrote:

Create a union query of the two queries. Then base your report on it.

-Tom.
Microsoft Access MVP
I have a 2 unmatched queries
Table1_view (records in table1 not in table2)
Table2_view (records in table2 not in table1)
Both table contain fields
ReportID
ReportName
ReportOwner
Thanks for the response - The Union query works fine but my report is
extremely slow.
Any suggestions on what to look at?
My database has no more than 5000 records.

Thanks

In_Table1 (True or False)
In_Table2 (True or False)
I need to create a single report that has all 5 fields no matter what
query it comes from
Group by ReportOwner
Show ReportID, ReportName and (If In_Table1 or In_Table2 is true show
a green box or
If In_Table1 or In_Table2 is false show a red box)
Any guidance or suggestions? Is this possible- Hide quoted text -

- Show quoted text -
Oct 30 '08 #3
On Thu, 30 Oct 2008 14:04:27 -0700 (PDT), Steve <da********@yahoo.com>
wrote:

Make sure you have indexes on the fields used in the WHERE clause.

Post the SQL; perhaps we can see what's up.

-Tom.
Microsoft Access MVP
>On Oct 30, 8:32*am, Tom van Stiphout <tom7744.no.s...@cox.netwrote:
>On Thu, 30 Oct 2008 04:13:27 -0700 (PDT), Steve <dafella...@yahoo.com>
wrote:

Create a union query of the two queries. Then base your report on it.

-Tom.
Microsoft Access MVP
>I have a 2 unmatched queries
Table1_view (records in table1 not in table2)
Table2_view (records in table2 not in table1)
>Both table contain fields
ReportID
ReportName
ReportOwner
Thanks for the response - The Union query works fine but my report is
extremely slow.
Any suggestions on what to look at?
My database has no more than 5000 records.

Thanks

>In_Table1 (True or False)
In_Table2 (True or False)
>I need to create a single report that has all 5 fields no matter what
query it comes from
Group by ReportOwner
Show ReportID, ReportName and (If In_Table1 or In_Table2 is true show
a green box or
If In_Table1 or In_Table2 is false show a red box)
>Any guidance or suggestions? Is this possible- Hide quoted text -

- Show quoted text -
Oct 31 '08 #4
Here is my SQL
SELECT tbl_ReportSummary.[Report ID] AS ID,
tbl_ReportSummary.Report AS ReportName,
tbl_ReportSummary.REPORT_OWNER AS NAME,
"True" AS [In IMDS],
"False" AS [In ResourcePlanner]

FROM Resources INNER JOIN (tbl_ReportSummary LEFT JOIN
PlannerMasterTable ON tbl_ReportSummary.[Report ID] =
PlannerMasterTable.IMDS_Report_ID) ON Resources.ResourceName =
tbl_ReportSummary.REPORT_OWNER
WHERE (((PlannerMasterTable.IMDS_Report_ID) Is Null));

UNION SELECT PlannerMasterTable.IMDS_Report_ID AS ID,
PlannerMasterTable.Report_Project_Name AS ReportName,
Resources.ResourceName AS NAME,
"False" AS [In IMDS],
"True" AS [In ResourcePlanner]
FROM Resources INNER JOIN (PlannerMasterTable LEFT JOIN
tbl_ReportSummary ON PlannerMasterTable.IMDS_Report_ID =
tbl_ReportSummary.[Report ID]) ON Resources.ResourceID =
PlannerMasterTable.Associate_key
WHERE (((tbl_ReportSummary.[Report ID]) Is Null));


On Oct 30, 8:31*pm, Tom van Stiphout <tom7744.no.s...@cox.netwrote:
On Thu, 30 Oct 2008 14:04:27 -0700 (PDT), Steve <dafella...@yahoo.com>
wrote:

Make sure you have indexes on the fields used in the WHERE clause.

Post the SQL; perhaps we can see what's up.

-Tom.
Microsoft Access MVP
On Oct 30, 8:32*am, Tom van Stiphout <tom7744.no.s...@cox.netwrote:
On Thu, 30 Oct 2008 04:13:27 -0700 (PDT), Steve <dafella...@yahoo.com>
wrote:
Create a union query of the two queries. Then base your report on it.
-Tom.
Microsoft Access MVP
I have a 2 unmatched queries
Table1_view (records in table1 not in table2)
Table2_view (records in table2 not in table1)
Both table contain fields
ReportID
ReportName
ReportOwner
Thanks for the response - The Union query works fine but my report is
extremely slow.
Any suggestions on what to look at?
My database has no more than 5000 records.
Thanks
In_Table1 (True or False)
In_Table2 (True or False)
I need to create a single report that has all 5 fields no matter what
query it comes from
Group by ReportOwner
Show ReportID, ReportName and (If In_Table1 or In_Table2 is true show
a green box or
If In_Table1 or In_Table2 is false show a red box)
Any guidance or suggestions? Is this possible- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -
Oct 31 '08 #5
On Fri, 31 Oct 2008 03:30:50 -0700 (PDT), Steve <da********@yahoo.com>
wrote:

Hmmm, pretty straightforward. I would not use Name as a column name
because it is a reserved word, and perhaps UNION ALL can be used, but
otherwise I don't see it. Someone else?

-Tom.
Microsoft Access MVP

>Here is my SQL
SELECT tbl_ReportSummary.[Report ID] AS ID,
tbl_ReportSummary.Report AS ReportName,
tbl_ReportSummary.REPORT_OWNER AS NAME,
"True" AS [In IMDS],
"False" AS [In ResourcePlanner]

FROM Resources INNER JOIN (tbl_ReportSummary LEFT JOIN
PlannerMasterTable ON tbl_ReportSummary.[Report ID] =
PlannerMasterTable.IMDS_Report_ID) ON Resources.ResourceName =
tbl_ReportSummary.REPORT_OWNER
WHERE (((PlannerMasterTable.IMDS_Report_ID) Is Null));

UNION SELECT PlannerMasterTable.IMDS_Report_ID AS ID,
PlannerMasterTable.Report_Project_Name AS ReportName,
Resources.ResourceName AS NAME,
"False" AS [In IMDS],
"True" AS [In ResourcePlanner]
FROM Resources INNER JOIN (PlannerMasterTable LEFT JOIN
tbl_ReportSummary ON PlannerMasterTable.IMDS_Report_ID =
tbl_ReportSummary.[Report ID]) ON Resources.ResourceID =
PlannerMasterTable.Associate_key
WHERE (((tbl_ReportSummary.[Report ID]) Is Null));


On Oct 30, 8:31*pm, Tom van Stiphout <tom7744.no.s...@cox.netwrote:
>On Thu, 30 Oct 2008 14:04:27 -0700 (PDT), Steve <dafella...@yahoo.com>
wrote:

Make sure you have indexes on the fields used in the WHERE clause.

Post the SQL; perhaps we can see what's up.

-Tom.
Microsoft Access MVP
>On Oct 30, 8:32*am, Tom van Stiphout <tom7744.no.s...@cox.netwrote:
On Thu, 30 Oct 2008 04:13:27 -0700 (PDT), Steve <dafella...@yahoo.com>
wrote:
>Create a union query of the two queries. Then base your report on it.
>-Tom.
Microsoft Access MVP
>I have a 2 unmatched queries
Table1_view (records in table1 not in table2)
Table2_view (records in table2 not in table1)
>Both table contain fields
ReportID
ReportName
ReportOwner
Thanks for the response - The Union query works fine but my report is
extremely slow.
Any suggestions on what to look at?
My database has no more than 5000 records.
>Thanks
>In_Table1 (True or False)
In_Table2 (True or False)
>I need to create a single report that has all 5 fields no matter what
query it comes from
Group by ReportOwner
Show ReportID, ReportName and (If In_Table1 or In_Table2 is true show
a green box or
If In_Table1 or In_Table2 is false show a red box)
>Any guidance or suggestions? Is this possible- Hide quoted text -
>- Show quoted text -- Hide quoted text -

- Show quoted text -
Oct 31 '08 #6
Tom van Stiphout wrote:
On Fri, 31 Oct 2008 03:30:50 -0700 (PDT), Steve <da********@yahoo.com>
wrote:

Hmmm, pretty straightforward. I would not use Name as a column name
because it is a reserved word, and perhaps UNION ALL can be used, but
otherwise I don't see it. Someone else?
The OP should run the query to see if the query outside the report to
determine if it is fast or slow. If slow then your recommendation on
indexes should be verified. 5000 records is quite small and it should
be near instantaneous.

It it's slow in the report, it might be the method used for changing
colors from red and green or some code behind the form.

>
-Tom.
Microsoft Access MVP
>>Here is my SQL
SELECT tbl_ReportSummary.[Report ID] AS ID,
tbl_ReportSummary.Report AS ReportName,
tbl_ReportSummary.REPORT_OWNER AS NAME,
"True" AS [In IMDS],
"False" AS [In ResourcePlanner]
>>FROM Resources INNER JOIN (tbl_ReportSummary LEFT JOIN
>>PlannerMasterTable ON tbl_ReportSummary.[Report ID] =
PlannerMasterTable.IMDS_Report_ID) ON Resources.ResourceName =
tbl_ReportSummary.REPORT_OWNER
WHERE (((PlannerMasterTable.IMDS_Report_ID) Is Null));

UNION SELECT PlannerMasterTable.IMDS_Report_ID AS ID,
PlannerMasterTable.Report_Project_Name AS ReportName,
Resources.ResourceName AS NAME,
"False" AS [In IMDS],
"True" AS [In ResourcePlanner]
>>FROM Resources INNER JOIN (PlannerMasterTable LEFT JOIN
>>tbl_ReportSummary ON PlannerMasterTable.IMDS_Report_ID =
tbl_ReportSummary.[Report ID]) ON Resources.ResourceID =
PlannerMasterTable.Associate_key
WHERE (((tbl_ReportSummary.[Report ID]) Is Null));


On Oct 30, 8:31 pm, Tom van Stiphout <tom7744.no.s...@cox.netwrote:
>>>On Thu, 30 Oct 2008 14:04:27 -0700 (PDT), Steve <dafella...@yahoo.com>
wrote:

Make sure you have indexes on the fields used in the WHERE clause.

Post the SQL; perhaps we can see what's up.

-Tom.
Microsoft Access MVP


On Oct 30, 8:32 am, Tom van Stiphout <tom7744.no.s...@cox.netwrote:

>On Thu, 30 Oct 2008 04:13:27 -0700 (PDT), Steve <dafella...@yahoo.com>
>wrote:

>Create a union query of the two queries. Then base your report on it.

>-Tom.
>Microsoft Access MVP

>>I have a 2 unmatched queries
>>Table1_view (records in table1 not in table2)
>>Table2_view (records in table2 not in table1)

>>Both table contain fields
>>ReportID
>>ReportName
>>ReportOwner

Thanks for the response - The Union query works fine but my report is
extremely slow.
Any suggestions on what to look at?
My database has no more than 5000 records.

Thanks

>>In_Table1 (True or False)
>>In_Table2 (True or False)

>>I need to create a single report that has all 5 fields no matter what
>>query it comes from
>>Group by ReportOwner
>>Show ReportID, ReportName and (If In_Table1 or In_Table2 is true show
>>a green box or
>>If In_Table1 or In_Table2 is false show a red box)

>>Any guidance or suggestions? Is this possible- Hide quoted text -

>- Show quoted text -- Hide quoted text -

- Show quoted text -
Oct 31 '08 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: dog | last post by:
I've seen plenty of articles on this topic but none of them have been able to solve my problem. I am working with an Access 97 database on an NT4.0 machine, which has many Access reports. I...
1
by: James N | last post by:
Here's the situation: I have 3 webforms in my project. Form 1 allows the user to select a type of report to generate. There are 2 possible type of reports, and therefore, Forms 2 and 3 are these...
9
by: Allan Ebdrup | last post by:
Hi I'm having a discussion with another programmer about a best practice for thrownig exceptions. I want to create a rich class library with many different exception classes in their own...
4
by: VMI | last post by:
For my website, I created CrystalReportTest.rpt (through the designer in VS 2005) and I'd like to be able to display it in viewer.aspx (all in the same project). Viewer.aspx has a Crystal Report...
2
by: angie | last post by:
I need to figure out how to create a user interface to search a query, but here's the bad part...I need to account for criteria on at least 7 of the fields. Here's what I'm thinking I need to do:...
6
by: fieldja | last post by:
I have a form called OwnerForm. It contains a combo box called Owner. The combo box looks up names from a table called OwnerName. It contains fields called OwnerID and Owner. I also have a main...
5
by: Linds | last post by:
I have a report within my access database that I would like to have a shortcut on my desktop that could bring up that report. In other words, now I have to double click on the database, then go...
3
by: creative1 | last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
1
by: Faith0999 | last post by:
I am currently a novice to using MS-Access. I currently have a report that I created from the following query. SELECT item.id, item.type, item.location, item.shipdate FROM Item WHERE item.type...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.