Organizing multiple queries

[Guest]

Hello to whoever is curious enough to read this post. Hope you are having a
wonderful day.

My problem is that I have 9 different queries which pull information for 9
different reports. The queries all do the same exact thing, based on a date
that I am prompted to enter and which report I am using (they are all a
little different). What I would like to do is organize the queries in such a
way that I can use either a combo box or a list box to select which report I
am running and input a date to get the information specific to that date. If
anyone has any ideas I would be forever grateful!

Thank you!

Teri.

 

[Rick B]

If the queries all do exactly the same thing, why not just have one query
and use it as the record source for all of your reports?

 

[Guest]

Use a form with unbound check boxes for reports and text box for entering
dates. Use a command button to run a macro with conditions reading which
check box is checked to select reports. The queries for the reports us the
text box from the form for date criteria.

 

[Guest]

I am not real sure how to do that. I am still learning all this confusing
stuff. Each of my multiple queries is named corresponding to the name of the
report it pulls information for. These reports are to be used as packing
slips for medical equipment to be shipped out. Each of the slips lists
exactly what is normally included in the "package" the customer orders (ie-
the Med Sys packing slip lists all the components which make up that package,
the Stress packing slip lists all the conponents which make up that package,
etc.) and how many of each of those components were ordered. The only fields
that do not change from form to form is Shipped to, date, record, shipping
method, software, software version and additional items ordered (which are
miscellaneous items that are not normally included in the package being
ordered). The form which is used to input the information into the database
includes a combo box which is used to select which packing slip is to be used
for that order. Other than date, this is the field that the query is based
on.

Can you suggest how I would work the query? I was thinking more along the
lines of what Karl Dewey suggested in his post, but if having a single query
would work, I would rather do that because it would cause less confusion for
whoever takes my place when I leave this "hole".

Thank you again,

Teri.

 

[Rick B]

If each query pulls the same data, then just save one named query. When you
open a report in design-view, go to the properties box and look at what is
in the "Record Source" on the data tab. Just change this to be the same
query for each of your reports (again, assuming that your query pulls all
the data needed for each report).

I'm still not sure why you have nine reports. a packing slip is a packing
slip. The items ordered would appear in the detail section of the packing
slip, but the header and footer should not be different. It sounds like you
have hard-coded each slip to list items. Do you not have a table where you
selected the items? Maybe I am confused on the structure of your tables.

If you are telling the database what product is ordered, then it seems like
you would build one report that would print different items depending on
what was ordered. I don't see why you'd build a separate report for each
item you carry. Does that mean that if you add a new product line, you will
have to create a new report? If so, your data structure is wrong. Any time
you have to have a developer step in to change the design of your database
when you expand your product offerings, you can be sure you have a
poorly-designed database. Your users should be able to add new products
without the need to create new tables, queries, forms, or reports.

In any case, if all you are pulling in your query is the customer shipping
information, then you just need one query. You can create as many reports
as you want using one stored query. The name of the query does not need to
match the name of the report. Your query name could be something like
"PackingSlipData".

I do think that you may have some work you could do to get your data and
objects more normalized. But without knowing a lot more about your
structure, it would be hard to advise.

Hope that helps.

 

[Guest]

You are right, the database, considering it is the very first one I have ever
done and created it with no training in Access (I have been teaching myself
as I go), is VERY poorly done. The boss wants a separate packing slip for
each product (otherwise phrased as package due to the fact that each package
includes several products, some of which are the same as one or more of some
of the other packages). At this point I am just trying to make what I have
useable while I get a better one designed. I have downloaded a template
from Microsoft's offerings that I want to run by him to save myself some time
and gray hair.

Each slip IS "hard coded", if you mean what I think you mean. I pulled the
fields from the field list for specific items for each report. Using the
Stress slip as an example, it lists 6 items that make up the package. From
customer order to customer order, the only thing that changes is the quantity
of each item ordered for that package, the customer name, date, record
number, shipping date and method of shipping. If the customer doesn't want a
certain item in the package it returns a "0" in the quantity field. I much
prefer the idea of using just one packing slip for all packages myself, but,
as stated previously, the boss wants what he wants.

In the meantime, in setting up the query to ask which packing slip I want to
use, would I put it in the same way I set it up to prompt me for a date (in
the design view of the query I typed in [Enter Date] as the criteria for the
date field)? But type in [Enter Packing Slip to use], or something similar
to that?

Thanks again!!!!

Teri.

 

[Rick B]

In my opinion, the query would not ask you which packing slip to print.

You would select the appropriate report (one for each type of packing slip).
You are putting the cart before the horse in a way. Don't let the user
select which query to run, have them select which report to run.

If they double-click the Med Sys report, then a Med Sys packing slip would
be created.

This is the way to do it with your current structure.

If you create ONE report to do everything, then you would need to ask the
user which type of slip to print. This would be either a prompt that pops
up or a form that has a drop-down. The report would then print differently
depending on which type the user selected. This could be done by coding the
report, or by modifying what the query pulls.


--
Rick B

You are right, the database, considering it is the very first one I have
ever
done and created it with no training in Access (I have been teaching
myself
as I go), is VERY poorly done. The boss wants a separate packing slip for
each product (otherwise phrased as package due to the fact that each
package
includes several products, some of which are the same as one or more of
some
of the other packages). At this point I am just trying to make what I
have
useable while I get a better one designed. I have downloaded a template
from Microsoft's offerings that I want to run by him to save myself some
time
and gray hair.

Each slip IS "hard coded", if you mean what I think you mean. I pulled
the
fields from the field list for specific items for each report. Using the
Stress slip as an example, it lists 6 items that make up the package.
From
customer order to customer order, the only thing that changes is the
quantity
of each item ordered for that package, the customer name, date, record
number, shipping date and method of shipping. If the customer doesn't
want a
certain item in the package it returns a "0" in the quantity field. I
much
prefer the idea of using just one packing slip for all packages myself,
but,
as stated previously, the boss wants what he wants.

In the meantime, in setting up the query to ask which packing slip I want
to
use, would I put it in the same way I set it up to prompt me for a date
(in
the design view of the query I typed in [Enter Date] as the criteria for
the
date field)? But type in [Enter Packing Slip to use], or something
similar
to that?

Thanks again!!!!

Teri.

If each query pulls the same data, then just save one named query. When
you
open a report in design-view, go to the properties box and look at what
is
in the "Record Source" on the data tab. Just change this to be the same
query for each of your reports (again, assuming that your query pulls all
the data needed for each report).

I'm still not sure why you have nine reports. a packing slip is a
packing
slip. The items ordered would appear in the detail section of the
packing
slip, but the header and footer should not be different. It sounds like
you
have hard-coded each slip to list items. Do you not have a table where
you
selected the items? Maybe I am confused on the structure of your tables.

If you are telling the database what product is ordered, then it seems
like
you would build one report that would print different items depending on
what was ordered. I don't see why you'd build a separate report for each
item you carry. Does that mean that if you add a new product line, you
will
have to create a new report? If so, your data structure is wrong. Any
time
you have to have a developer step in to change the design of your
database
when you expand your product offerings, you can be sure you have a
poorly-designed database. Your users should be able to add new products
without the need to create new tables, queries, forms, or reports.

In any case, if all you are pulling in your query is the customer
shipping
information, then you just need one query. You can create as many
reports
as you want using one stored query. The name of the query does not need
to
match the name of the report. Your query name could be something like
"PackingSlipData".

I do think that you may have some work you could do to get your data and
objects more normalized. But without knowing a lot more about your
structure, it would be hard to advise.

Hope that helps.