Re: Sum of numbers

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Evi...I thought I could build the queries from the linked tables, but then
realized I have to do them in each database then import to the main database.
I got underway today through the 3rd table and stopped because that table
has 9 fields while the first 2 have only 8. I have to use the 9th field
because the product comes in 2 sizes. How do I account for the 9th field in
the tables that have 8. Your notes say I must have the same number of fields.

"Evi" wrote:

The secret to making it error free, is to make the Union Query with queries
where the filtering/calculations have been done on the queries on which your
final queries are based so that all you have to do is type in the field
names and query names, following the 'recipe' below. Remember, you won't
need to sort your queries - that will be done in the Report. So if you want
to filter your list, filter it in one query then go to Insert Query and put
all the fields from your filtered query into a new query. Eventually,
you'll be able to do it the cleverpants way and will not need all those
superfluous queries but for now, try it this way..
I found it really unnerving doing a query that didn't have a Design View -
I thought it would blow up or something if I got it wrong.

If it helps, I can send you a simple example of one in action so that you
can take it to bits and see its innards.

Tell us if you get it to work, eh?

Evi


"ftwguy" <ftwguy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E5DDC71C-0D21-48E3-B6F2-37F123CC5FC8@xxxxxxxxxxxxxxxx
Evi...I will try the Union query again today. I tried yesterday for hours
and kept getting errors. It may be above my experience level in Access.

"Evi" wrote:

A union query may be able to do this, unless I have misunderstood you.
Do
try it. In the report itself, you then use the Sorting/Grouping box to
group
the resulting report by customer ID and use the ForceNewPage Property
after
the CustomerID Footer. Your Totals can go in this footer and the Overall
Total for all the customers can go in the report footer so that each
customer gets their own report. I tried it on 3 completely unlinked
tables
and it did work. Remember, nothing is added to the query grid when you
do a
Union Query. It is done purely by SQL
Evi

"ftwguy" <ftwguy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0581A090-57A0-4157-9745-87E132FBA9D5@xxxxxxxxxxxxxxxx
Is the linked field the customer one so that say the customer's ID
number
appears in a column of each table where he buys the product listed in
that
table?

YES ! Each customer is linked by that unique ID. An example is
COR3257,
COR6543, HOU2548 and so forth. That is the "join" I use between the
linked
tables.

Like I said, the report works fine as long as that unique ID is in all
5
tables for a each particular product. However, if it looks for an ID
like
COR3257 in the YUMI table, and it is not there, then it serves up the
"blank
value" instead of an integer. I can't help it if the customer didn't
buy
Yumi.

I'm so close yet so far. I got 3 weeks to solve this before the
reports
are
do. The irony is I can do all the individual reports for each
product,
but
the boss demands all products appear on ONE report. He wants to give
customers a one-page report, and not one-page for each of the 5 or
more
products.

Yep, I inherited a real mess. It stresses me each day! :)

"Evi" wrote:

Wow!!! An Object Lesson to frighten learners with the disadvantages
of
bad
database design. Your Access expert, when he arrives, will be making
loads
of those sucking in noises that mechanics make when your car is a
write-off.

I just can't picture your tables.

Is the linked field the customer one so that say the customer's ID
number
appears in a column of each table where he buys the product listed
in
that
table? Do the fields which you wish to use have the same field names
within
their tables?

I do think a Union query is the way to go but you will probably need
to
build queries on queries to get the effect you want. (click on a
query
and
go to Insert, Query and drag all the fields from the query into the
query
grid)
Do as much 'arithmetic' within the queries so that if the customer
buys
12
eggs at 5p each have the results in the query with a field like
NetCost:[ProductPrice]*[QuantityBought] rather than putting this
calculation
in the report.
Null fields aren't too bad if you are just adding up but they hate
anything
else.

To make it easier to type in SQL, base query upon query so that your
final
queries don't have any calculations in it (the query based on the
query
which has the NetCost calculation will simply have a field called
NetCost.
To create a Union query, go to Query, New Query, but don't add any
tables to
it. Click the View button and choose Sql so that you get a blank
***.

The 'recipe' for a Union ALL query would be something like this
SELECT ClientNumber, ClienttName, Prodc, ProdCCost FROM Query1
UNION ALL
SELECT CustNum, CustName,Prod, ProdCost FROM Query2
UNION ALL
SELECT ClientNumber,ClientName,Prod, ProdCost FROM Query3;

You'll see that
1. Each query has the same number of fields,
2. the field names can be different for each query but the data
types
must
be the same so my first field is the Number field, the second field
is a
text field etc.
3. They come in the same order in each query the Customer Number
field
then
the Customer Name field etc.

Of course you will substitute your own field and query names.

It may be easiest to write the query in Word so that you can see the
Querys'
field names and then paste it into Access

You'll see all the Customers listed with the products they bought.
The
query
will choose the field names from the first query which you add.


Click on this Union Query and choose Insert, Report.

Of course, your tables are much more complex that this but do you
think
you
can adapt this 'recipe' to suit?

Evi











"ftwguy" <danno467@xxxxxxxxxxxxxxxx> wrote in message
news:8D1246C5-74DB-46E7-A5A9-7800F0BA7713@xxxxxxxxxxxxxxxx
Evi....you won't believe it, but one region may have 225 or max
data
columns
in it along with 2000 records. I kid you not. One product in one
region
may
require 36 columns based on the way the previous employee set up
the
tables.
They wanted to add more columns but thankfully MS Access wouldn't
allow
more.

I finally pulled out select products and their related columns and
built
seperate tables to reliveve the stress on each of the 5 original
tables.
I've managed to keep it all running, all queries and reports
working
EXCEPT
for this final and most critical report on the sales of the
products
for
each
customer.

Yes, I tried Union Query but quite dicey and I'm not at that level
of
expertise. I may just have to try to find a MS Access Expert in
the
Fort
Worth/Dallas area to come here and try to solve this report issue.
I
am
running out of time.



"Evi" wrote:

Having '34 products throughout 5 regions' does not mean you will
have
'data
columns into the hundreds'. You would have the same number of
columns as
if
you had 900 products throughout 900 regions. Once the database
is
designed
properly, you can add new products and regions without having to
add
new
columns.

But I can well imagine the problem you are faced with, having to
work
with
what you've got. I don't suppose they'll let you anywhere near
the
database
to redesign it from the ground up even if you wanted to.

Have you tried using a Union query? They are a bit piggy to do,
but
they
are
one way of joining up tables which would otherwise be unrelated
(in
the
Access sense of the word).
Evi



"ftwguy" <danno467@xxxxxxxxxxxxxxxx> wrote in message
news:D7E0A09B-21DE-401B-AC3D-307CAA1B7970@xxxxxxxxxxxxxxxx
Evi: I need to see all products ordered or not ordered for
each
customer.
I
understand the need to have separate tables, but right now
that is
not
possible, as we offer 34 products throughout 5 regions, and
will
have
data
columns into the hundreds. The company will eventually have
to
hire a
database design company to completely revamp. Right now I'm
stuck
with
"making it work" as is. I've only been here 5 months. I am
not a
programmer
nor expert in MS Access. I'd consider myself advanced
intermediate at
best.

I just really need to know if it is possible to make my 3rd
report
column
default to Zero (serve up a zero) if there is no data from one
of
the
tables.
Right now I get a "blank" if there is no data and that ruins
my
formula
that
sums up the total amount of the sales.



"Evi" wrote:

.

Quantcast