I need to combine two queries

[Guest]

I'm currently creating a serials database for a library, and what we are
trying to do it tack the subscription renewals on a set of direct
periodicals. What I'm trying to do , it create a query that will search the
database for a set of periodicals based on thier code. Then I want a new
field to be added to the database, suming up the cost of all the periodicals
with the accompanying code. (to track spending).

I'm trying to combine the following two sequel statements to complete this
task:

SELECT [Direct Serials].[Direct Number], [Direct Serials].Code, [Direct
Serials].Title, [Direct Serials].Publisher, [Direct Serials].Address, [Direct
Serials].[City/Province], [Direct Serials].[Postal Code], [Direct
Serials].Email, [Direct Serials].[Web Address], [Direct Serials].[Renewal
Date], [Direct Serials].Cost, [Direct Serials].[Method Paid], [Direct
Serials].[Subscription Type], [Direct Serials].[Sub - Start], [Direct
Serials].[Sub - End], [Direct Serials].Frequency
FROM [Direct Serials]
WHERE ((([Direct Serials].Code)=[Enter Code:])); (which works) and also

SELECT [Direct Serials].Cost, Sum([Direct Serials].Cost) AS SumOfCost
FROM [Direct Serials]
GROUP BY [Direct Serials].Cost;

Is there a way I can make these work together ?

In addition, how can I get the results of a query to display in a form as
opposed to in table view. ?

Any insight on this problem would be greatly appreciated.

Sincerely,

Tiffany

 

[Jeff Boyce]

Perhaps I skimmed over it, but I didn't notice how these two queries could
connect -- i.e., a common field.

Or, if the data is coming from two sources, but has the same number of
corresponding fields in each query, take a look at a UNION query.

 

[Guest]

They do have a common feild, the common field is Direct Serials.Cost, what I
need to do is sum up the cost fields (to get a total of the spending) that
has occurred in a department based on the query by code.

I hope this makes sense, I've been stuck on this problem for two days and
I'm not feeling very articulate.

I appreciate you help and I will look into the UNION query.

Perhaps I skimmed over it, but I didn't notice how these two queries could
connect -- i.e., a common field.

Or, if the data is coming from two sources, but has the same number of
corresponding fields in each query, take a look at a UNION query.

--
Good luck

Jeff Boyce
<Access MVP>

I'm currently creating a serials database for a library, and what we are
trying to do it tack the subscription renewals on a set of direct
periodicals. What I'm trying to do , it create a query that will search the
database for a set of periodicals based on thier code. Then I want a new
field to be added to the database, suming up the cost of all the periodicals
with the accompanying code. (to track spending).

I'm trying to combine the following two sequel statements to complete this
task:

SELECT [Direct Serials].[Direct Number], [Direct Serials].Code, [Direct
Serials].Title, [Direct Serials].Publisher, [Direct Serials].Address, [Direct
Serials].[City/Province], [Direct Serials].[Postal Code], [Direct
Serials].Email, [Direct Serials].[Web Address], [Direct Serials].[Renewal
Date], [Direct Serials].Cost, [Direct Serials].[Method Paid], [Direct
Serials].[Subscription Type], [Direct Serials].[Sub - Start], [Direct
Serials].[Sub - End], [Direct Serials].Frequency
FROM [Direct Serials]
WHERE ((([Direct Serials].Code)=[Enter Code:])); (which works) and also

SELECT [Direct Serials].Cost, Sum([Direct Serials].Cost) AS SumOfCost
FROM [Direct Serials]
GROUP BY [Direct Serials].Cost;

Is there a way I can make these work together ?

In addition, how can I get the results of a query to display in a form as
opposed to in table view. ?

Any insight on this problem would be greatly appreciated.

Sincerely,

Tiffany

 

[Jeff Boyce]

Perhaps I don't understand your situation -- you are trying to match up the
two queries based on Cost? What happens if two elements in the first query
have the same cost as one element in the second?

Jeff Boyce
<Access MVP>

They do have a common feild, the common field is Direct Serials.Cost, what I
need to do is sum up the cost fields (to get a total of the spending) that
has occurred in a department based on the query by code.

I hope this makes sense, I've been stuck on this problem for two days and
I'm not feeling very articulate.

I appreciate you help and I will look into the UNION query.

Perhaps I skimmed over it, but I didn't notice how these two queries could
connect -- i.e., a common field.

Or, if the data is coming from two sources, but has the same number of
corresponding fields in each query, take a look at a UNION query.

--
Good luck

Jeff Boyce
<Access MVP>

I'm currently creating a serials database for a library, and what we are
trying to do it tack the subscription renewals on a set of direct
periodicals. What I'm trying to do , it create a query that will

search
the

database for a set of periodicals based on thier code. Then I want a new
field to be added to the database, suming up the cost of all the periodicals
with the accompanying code. (to track spending).

I'm trying to combine the following two sequel statements to complete this
task:

SELECT [Direct Serials].[Direct Number], [Direct Serials].Code, [Direct
Serials].Title, [Direct Serials].Publisher, [Direct Serials].Address, [Direct
Serials].[City/Province], [Direct Serials].[Postal Code], [Direct
Serials].Email, [Direct Serials].[Web Address], [Direct Serials].[Renewal
Date], [Direct Serials].Cost, [Direct Serials].[Method Paid], [Direct
Serials].[Subscription Type], [Direct Serials].[Sub - Start], [Direct
Serials].[Sub - End], [Direct Serials].Frequency
FROM [Direct Serials]
WHERE ((([Direct Serials].Code)=[Enter Code:])); (which works) and also

SELECT [Direct Serials].Cost, Sum([Direct Serials].Cost) AS SumOfCost
FROM [Direct Serials]
GROUP BY [Direct Serials].Cost;

Is there a way I can make these work together ?

In addition, how can I get the results of a query to display in a form as
opposed to in table view. ?

Any insight on this problem would be greatly appreciated.

Sincerely,

Tiffany

 

[Guest]

So I have a table that tracks periodicals, the fields we are most concerned
with are the cost field and the code field. All periodicals are sorted by
code based on thier subject matter. The cost field states the cost of each
magazine subscription. What I want the user to do, is to be able to type in
a code (say 1400) and be able to see all the magazine listings under code
1400 and a sum of the cost of the magazines for this department.

I checked out the union statement and that will not work here, because all
the information is in one table.

Thank you for your time and consideration.

Tiff

They do have a common feild, the common field is Direct Serials.Cost, what I
need to do is sum up the cost fields (to get a total of the spending) that
has occurred in a department based on the query by code.

I hope this makes sense, I've been stuck on this problem for two days and
I'm not feeling very articulate.

I appreciate you help and I will look into the UNION query.

Perhaps I skimmed over it, but I didn't notice how these two queries could
connect -- i.e., a common field.

Or, if the data is coming from two sources, but has the same number of
corresponding fields in each query, take a look at a UNION query.

--
Good luck

Jeff Boyce
<Access MVP>

I'm currently creating a serials database for a library, and what we are
trying to do it tack the subscription renewals on a set of direct
periodicals. What I'm trying to do , it create a query that will search the
database for a set of periodicals based on thier code. Then I want a new
field to be added to the database, suming up the cost of all the periodicals
with the accompanying code. (to track spending).

I'm trying to combine the following two sequel statements to complete this
task:

SELECT [Direct Serials].[Direct Number], [Direct Serials].Code, [Direct
Serials].Title, [Direct Serials].Publisher, [Direct Serials].Address, [Direct
Serials].[City/Province], [Direct Serials].[Postal Code], [Direct
Serials].Email, [Direct Serials].[Web Address], [Direct Serials].[Renewal
Date], [Direct Serials].Cost, [Direct Serials].[Method Paid], [Direct
Serials].[Subscription Type], [Direct Serials].[Sub - Start], [Direct
Serials].[Sub - End], [Direct Serials].Frequency
FROM [Direct Serials]
WHERE ((([Direct Serials].Code)=[Enter Code:])); (which works) and also

SELECT [Direct Serials].Cost, Sum([Direct Serials].Cost) AS SumOfCost
FROM [Direct Serials]
GROUP BY [Direct Serials].Cost;

Is there a way I can make these work together ?

In addition, how can I get the results of a query to display in a form as
opposed to in table view. ?

Any insight on this problem would be greatly appreciated.

Sincerely,

Tiffany

 

[Jeff Boyce]

Are you saying that you want the sum of all Cost amounts when Code = (say)
1400?

Take a look at a Totals query, GroupBy the Code and Sum the Cost. Now add a
parameter for entry of the Code.

A more user friendly way to do this is to use a form and put a combo box on
it with the list of possible Codes. Put a command button on it that opens
the query (or report). Modify the query to "point to" the form's combo box
for its criterion, with something like:

Forms!frmYourFormName!cboYourComboBoxName

in the criterion section under the Code field.