displaying two query results together

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two queries sorting info from the same table. I want to group customer
information by their type of protocol but also in alphabetical order by their
ID. The two types of protocols are high-speed (sftp, https, vpn) and dial-up
(async, bisync). I want to display the results of one query followed by the
results of the other query. Here is an example:

ID Protocol
AA101 SFTP
AA103 HTTPS
AD124 VPN
AD125 SFTP
AD128 HTTPS
AATWAY ASYNC
AB56CO ASYNC
ACERT BISYNC
BA12R ASYNC
CROMWL ASYNC
DRORW BISYNC
DSRPT ASYNC

I just want to display one right after the other. No
joining/blending/editing results.
 
I just want to display one right after the other. No
joining/blending/editing results.
Click to expand...

The simplest way would be to use a Report (or Form) with two
Subreports (subforms), based on the two queries.

A UNION query may be possible as well but the sorting may be a
problem.

John W. Vinson[MVP]
 
I wondered if that was just too much to expect from a query. I tried the two
subreports before but I couldn't get it to work. After your suggestion, I
tried again and was able to get it. Thanks for your input.
 
Oh my. An unexpected problem came up with using 2 subreports. In each query,
I need to enter a time period that these queries will cover. This results in
being prompted twice for the same information to cover each query. I only
want to be prompted once to cover both queries. Is there a way to alter the
report/queries or do I need to somehow combine the two queries?
 
Is there a way to alter the
report/queries or do I need to somehow combine the two queries?
Click to expand...

Alter the Queries instead. Create a little unbound form, frmCrit, with
textboxes or other controls into which the user will enter the
criteria. Change the parameters on the queries to

=[Forms]![frmCrit]![controlname]

so the queries will look at the values on the Form; it's convenient to
put a command button on frmCrit to open the Report.

John W. Vinson[MVP]
 
Great idea! I'm working on that. However, I am trying to set up one of my
time period fields with a default value of 11/1/2005. I am trying to do this
in the properties of the (unbound) text box. I keep getting a default value
of 12/30/1899 displayed in the text box. I have tried a number of
combinations but I end up with the same result. What is going on?
I have Format: Short Date and Input Mask: Short Date
 
Harry said:
I have two queries sorting info from the same table. I want to group customer
information by their type of protocol but also in alphabetical order by their
ID. The two types of protocols are high-speed (sftp, https, vpn) and dial-up
(async, bisync). I want to display the results of one query followed by the
results of the other query. Here is an example:

ID Protocol
AA101 SFTP
AA103 HTTPS
AD124 VPN
AD125 SFTP
AD128 HTTPS
AATWAY ASYNC
AB56CO ASYNC
ACERT BISYNC
BA12R ASYNC
CROMWL ASYNC
DRORW BISYNC
DSRPT ASYNC

I just want to display one right after the other. No
joining/blending/editing results.
Click to expand...

Does each customer have one protocol? If yes then this is easy, no
grouping required.

Judging by your question maybe my assumption is not true? But then you
want to aggregate two disparate measures?
 
Great idea! I'm working on that. However, I am trying to set up one of my
time period fields with a default value of 11/1/2005. I am trying to do this
in the properties of the (unbound) text box. I keep getting a default value
of 12/30/1899 displayed in the text box. I have tried a number of
combinations but I end up with the same result. What is going on?
Click to expand...

It's dividing 11 by 1, and that result by 2005; this gives
0.005486284289276, which corresponds to #12/30/1899 00:07:54#.

Try setting the default value to #11/1/2005# to get November 1. The #
indicates a date value.

John W. Vinson[MVP]
 
For Smartin: Each customer has only one protocol.
For John Vinson: Thanks for helping with the date display issue. Below is
the exact code I have been using for “high-speed†customers.

SELECT [CLIENT TOTAL].Mailbox, [CLIENT TOTAL].Customer, [CLIENT
TOTAL].[CustID-DLB#], [CLIENT TOTAL].[Category-ID], [CLIENT
TOTAL].[Billing-ID], [CLIENT TOTAL].Fee, [CLIENT TOTAL].Currency, [CLIENT
TOTAL].[Date Added]
FROM [CLIENT TOTAL]
WHERE ((([CLIENT TOTAL].Fee)>0) AND (([CLIENT TOTAL].[Primary
Protocol])<>"Async" Or ([CLIENT TOTAL].[Primary Protocol])="Bisync" Or
([CLIENT TOTAL].[Primary Protocol])="SNA"))
ORDER BY [CLIENT TOTAL].Mailbox;

The “dial-up†customers query is exactly the same except there is an “equal
to†symbol in place of the “not equal to†symbol.

Now comes the sorting by the date they became a customer (for billing
purposes). I created a small form (SORT DATE) with two unbound text boxes.
SortDateA (default value is Date()) and SortDateB (default value is
#11/4/2005#). 11/4/2005 is when we got our first customer. So I want to be
able to create a billing report for that time period or anywhere in between.

According to your example, this is how the date code would be written (?)
=[Forms]![SORT DATE]![SortDateA]
=[Forms]![SORT DATE]![SortDateB]

Please help me join these bits of code to the main body of the query. I have
tried and I get prompted to Enter Parameter Values labeled: [Forms]![SORT
DATE]![SortDateA] and =[Forms]![SORT DATE]![SortDateB]

Also, when I enter the values in the SORT DATE form, is it enough to just
save the form and exit? Will the queries respond to those values that I enter
when I run the queries?
 
I created a command button to create a billing report for all customers so
that is taken care of. I attached the two text boxes to a table DATESORT. I
just need to configure each query to use the last saved date entries in the
DATESORT table as the Between parameters for the Date Added column in my
query. Of course, I might have to go back to Unbound text boxes but I believe
this could be a viable alternative. I’m continuing to work on it.

Harry said:
For Smartin: Each customer has only one protocol.
For John Vinson: Thanks for helping with the date display issue. Below is
the exact code I have been using for “high-speed†customers.

SELECT [CLIENT TOTAL].Mailbox, [CLIENT TOTAL].Customer, [CLIENT
TOTAL].[CustID-DLB#], [CLIENT TOTAL].[Category-ID], [CLIENT
TOTAL].[Billing-ID], [CLIENT TOTAL].Fee, [CLIENT TOTAL].Currency, [CLIENT
TOTAL].[Date Added]
FROM [CLIENT TOTAL]
WHERE ((([CLIENT TOTAL].Fee)>0) AND (([CLIENT TOTAL].[Primary
Protocol])<>"Async" Or ([CLIENT TOTAL].[Primary Protocol])="Bisync" Or
([CLIENT TOTAL].[Primary Protocol])="SNA"))
ORDER BY [CLIENT TOTAL].Mailbox;

The “dial-up†customers query is exactly the same except there is an “equal
to†symbol in place of the “not equal to†symbol.

Now comes the sorting by the date they became a customer (for billing
purposes). I created a small form (SORT DATE) with two unbound text boxes.
SortDateA (default value is Date()) and SortDateB (default value is
#11/4/2005#). 11/4/2005 is when we got our first customer. So I want to be
able to create a billing report for that time period or anywhere in between.

According to your example, this is how the date code would be written (?)
=[Forms]![SORT DATE]![SortDateA]
=[Forms]![SORT DATE]![SortDateB]

Please help me join these bits of code to the main body of the query. I have
tried and I get prompted to Enter Parameter Values labeled: [Forms]![SORT
DATE]![SortDateA] and =[Forms]![SORT DATE]![SortDateB]

Also, when I enter the values in the SORT DATE form, is it enough to just
save the form and exit? Will the queries respond to those values that I enter
when I run the queries?


John Vinson said:
It's dividing 11 by 1, and that result by 2005; this gives
0.005486284289276, which corresponds to #12/30/1899 00:07:54#.

Try setting the default value to #11/1/2005# to get November 1. The #
indicates a date value.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Below, I have summarized the issue I am having to make it easier for someone
to help me.
I have a report and use two queries as subreports. The queries are setup to
ask for start date and end date parameter info. When I run the report, I am
prompted twice to enter the same info for each query/subreport. I only want
to be prompted once and use those values for both queries/subreports.
With the help of John Vinson, I have been working on this issue. I created
a separate pop-up form with two unbound text boxes to enter the start and end
dates. I am wondering if there is a way to link those values in the form to
the two queries.
 
Back
Top