Sum of numbers

[ftwguy]

I have a report that pulls numbers from one main table and 4 linked tables.
The report is fairly basic showing a product quantity x price = total. The
quanity value comes from one of the 5 tables. Here's the problem. My report
needs to show 5 products, or one from each table. Howver, the customer may
not be a customer for a product in table 5 so it is not listed in that
table...therefore, no data to pull. I was hoping the control source would
display a Zero value for the quanity. It displays nothing. So I end up with:
5 x 2.00 = 10.00
7 x 1.50 = 10.50
3 x 2.00 = 6.00
7 x 3.00 = 21.00
6 x blank = blank

I need ZEROES to appear for the blank, but in the report there is no way to
set that to ZERO as a default if no number is pulled from the 5th table.
Can't pull something not there in the data. The only thing that controls it
is the 'control source'. I need these instances to display zero so I can
total the total price figures. My formula for the TOTAL AMOUNT won't display
either because the fifth value is blank.

Is there some code I can insert somewhere to make it show zero anytime there
is no data to pull from the "control source', or maybe some code I can add to
the formula for TOTAL AMOUNT that would ignore any BLANKS?

 

[Evi]

1. Have I misunderstood or do you put different products in different
tables.
Are you adding an extra table every time you want to add a product? I can't
imagine this. Please explain your database structure more fully. The sort of
structure I would expect is (basically) a Products table to list all your
products, a Customer table to list all your customers and a Customer Orders
table which contains a link to the Customers table and to the Products table
showing the Products which customers buy and how many they buy.
2.Have a look at Union Query in Help and see if that will give you what you
want when you need to add things from several different queries.
3.you can make a Null look like a 00.00 by using Format in the Properties
box when you are clicked on that control. Type 00.00 in the Format row. But
be aware that it is NOT really 0 so, although adding the positive numbers
will work, if you try for instance to have anything like 5 - Null, the
answer won't be 5 but will appear as 00.00 so test the report with small
numbers first to see that it is giving the results which you want.
4. Did you want to see all the customers even if they don't have orders or
did you want to see all the products even if they haven't been purchased or
both?


Evi

 

[ftwguy]

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.

 

[n0d]

In your querry, you can run a new column with this if command. it
states if the field customer is blank, returns 0 (zero), otherwise,
returns the value of price.

total:[quantity]*iif([customer] is null,0,[price])

Let me know it does it.

Don

 

[ftwguy]

I'm not doing queries. I get the data directly from 1 table and 4
additionalt tables linked to the main table. My report works great as long
as that customer is particiapting in the products from all 5 tables. If a
customer does not the product from table 2, 3, 4 or 5, then I get a "blank"
quantity value (for the product not bought) instead of a zero. I understand
I get the blank because that customer is not in that specific table since
he/she does not buy that product.

My control source might be 1Qtr Pretzels from Table 3, and I got to have a
way to serve up a Zero for that if he/she does not buy Pretzels, but he does
buy product from Tables 1, 2, 4, 5.

The 4th column in my report is the TOTAL. So in the above example, the
forumla is =[1Qtr Pretzels]*2.00 which serves up my total (as long as 1Qtr
Pretzels has a value from 0 and higher)

 

[Evi]

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]

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.

 

[n0d]

OK.. in that same formular in the 4th column. Rewrite in the control
source of the TOTAL formula.
=iif([1 Qtr Pretzels] is null,0, [1 Qtr Pretzels] *2)

Did it do it?

 

[Evi]

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 sheet.

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]

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!

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 sheet.

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

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]

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

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!

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 sheet.

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

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.



:

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



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.



:

1. Have I misunderstood or do you put different products in different
tables.
Are you adding an extra table every time you want to add a

product?
I

can't
imagine this. Please explain your database structure more fully. The
sort of
structure I would expect is (basically) a Products table to list all
your
products, a Customer table to list all your customers and a Customer
Orders
table which contains a link to the Customers table and to the Products
table
showing the Products which customers buy and how many they buy.
2.Have a look at Union Query in Help and see if that will give

you
what

you
want when you need to add things from several different queries.
3.you can make a Null look like a 00.00 by using Format in the
Properties
box when you are clicked on that control. Type 00.00 in the

Format
row.

But
be aware that it is NOT really 0 so, although adding the positive
numbers
will work, if you try for instance to have anything like 5 -

Null,
the

answer won't be 5 but will appear as 00.00 so test the report

with
small

numbers first to see that it is giving the results which you want.
4. Did you want to see all the customers even if they don't have orders
or
did you want to see all the products even if they haven't been purchased
or
both?


Evi

I have a report that pulls numbers from one main table and 4 linked
tables.
The report is fairly basic showing a product quantity x price

=
total.

The
quanity value comes from one of the 5 tables. Here's the

problem.
My

report
needs to show 5 products, or one from each table. Howver, the
customer
may
not be a customer for a product in table 5 so it is not listed

in
that

table...therefore, no data to pull. I was hoping the control source
would
display a Zero value for the quanity. It displays nothing.

So I
end

up
with:
5 x 2.00 = 10.00
7 x 1.50 = 10.50
3 x 2.00 = 6.00
7 x 3.00 = 21.00
6 x blank = blank

I need ZEROES to appear for the blank, but in the report there

is
no

way
to
set that to ZERO as a default if no number is pulled from the 5th
table.
Can't pull something not there in the data. The only thing that
controls
it
is the 'control source'. I need these instances to display

zero
so I

can
total the total price figures. My formula for the TOTAL

AMOUNT
won't

display
either because the fifth value is blank.

Is there some code I can insert somewhere to make it show zero anytime
there
is no data to pull from the "control source', or maybe some

code I
can

add
to
the formula for TOTAL AMOUNT that would ignore any BLANKS?

 

[ftwguy]

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.

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

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!

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 sheet.

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











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.



:

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



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.



:

1. Have I misunderstood or do you put different products in
different
tables.
Are you adding an extra table every time you want to add a product?
I
can't
imagine this. Please explain your database structure more fully. The
sort of
structure I would expect is (basically) a Products table to list all
your
products, a Customer table to list all your customers and a Customer
Orders
table which contains a link to the Customers table and to the
Products
table
showing the Products which customers buy and how many they buy.
2.Have a look at Union Query in Help and see if that will give you
what
you
want when you need to add things from several different queries.
3.you can make a Null look like a 00.00 by using Format in the
Properties
box when you are clicked on that control. Type 00.00 in the Format
row.
But
be aware that it is NOT really 0 so, although adding the positive
numbers
will work, if you try for instance to have anything like 5 - Null,
the
answer won't be 5 but will appear as 00.00 so test the report with
small
numbers first to see that it is giving the results which you want.
4. Did you want to see all the customers even if they don't have
orders
or
did you want to see all the products even if they haven't been
purchased
or
both?


Evi

I have a report that pulls numbers from one main table and 4
linked
tables.
The report is fairly basic showing a product quantity x price =
total.
The
quanity value comes from one of the 5 tables. Here's the problem.
My
report
needs to show 5 products, or one from each table. Howver, the
customer
may
not be a customer for a product in table 5 so it is not listed in
that
table...therefore, no data to pull. I was hoping the control
source

 

[Evi]

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

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.

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

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!

:

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 sheet.

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











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.



:

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



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.



:

1. Have I misunderstood or do you put different products in
different
tables.
Are you adding an extra table every time you want to add a product?
I
can't
imagine this. Please explain your database structure more

fully.
The

sort of
structure I would expect is (basically) a Products table to

list
all

your
products, a Customer table to list all your customers and a Customer
Orders
table which contains a link to the Customers table and to the
Products
table
showing the Products which customers buy and how many they buy.
2.Have a look at Union Query in Help and see if that will

give
you

what
you
want when you need to add things from several different queries.
3.you can make a Null look like a 00.00 by using Format in the
Properties
box when you are clicked on that control. Type 00.00 in the Format
row.
But
be aware that it is NOT really 0 so, although adding the positive
numbers
will work, if you try for instance to have anything like 5 - Null,
the
answer won't be 5 but will appear as 00.00 so test the

report
with

small
numbers first to see that it is giving the results which you want.
4. Did you want to see all the customers even if they don't have
orders
or
did you want to see all the products even if they haven't been
purchased
or
both?


Evi

I have a report that pulls numbers from one main table and 4
linked
tables.
The report is fairly basic showing a product quantity x

price
=

total.
The
quanity value comes from one of the 5 tables. Here's the problem.
My
report
needs to show 5 products, or one from each table. Howver, the
customer
may
not be a customer for a product in table 5 so it is not

listed
in

that
table...therefore, no data to pull. I was hoping the control
source

 

[ftwguy]

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.

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

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.

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

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!

:

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 sheet.

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











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.



:

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



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.



:

 

[Rob Parker]

You just add the additional field as a constant entry, set to Null, for the
tables with 8 fields.

If you use your third table in the first SELECT clause of your Union query,
you won't need to define an alias for this field; you'll have something
like:

SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9
FROM Table3
UNION
SELECT F1, F2, F3, F4, F5, F6, F7, F8, Null
FROM Table1
UNION ...

If you have one of the 8-field tables in the first SELECT clause, you just
alias the Null entry to the 9th fieldname in the first SELECT clause; you'll
have something like:

SELECT F1, F2, F3, F4, F5, F6, F7, F8, Null As F9
FROM Table1
UNION
SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9
FROM Table3
UNION ...

Note that UNION will eliminate duplicate records from the final result. If
you have duplicates and need to retain them, you must use UNION ALL

HTH,

Rob

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.

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

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.

:

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

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!

:

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 sheet.

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











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.



:

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



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]

Well, well, I've learnt something else new, Rob. So that's how its done!
And ftwguy, don't worry about the lack of linking. The union query works
fine on unlinked tables and queries so long as they follow the Union Rules.
In fact, you may be better off keeping them unlinked with the database
'structure' which you have inherited.

If by any chance, the extra field in the 9th table is a number one, which
you need to calculate, then you could avoid Nulls by adding a dummy field to
the queries based on the other tables
In your query grid type
Dummy:0

Now those queries will have a ninth field which says 0 in it.

Similarly, if it's a Yes/No field, then your dummy field can say 0 (if you
want it to say No) or -1 if you want it to say Yes)

Don't forget, your union query will not be based directly on the tables but
on the queries which are based on the tables (or even on the queries which
are based on the queries which are based on the tables.

Your boss had better give you a big fat bonus if you solve this one,
ftwguy - talk about making bricks without straw!
Evi

You just add the additional field as a constant entry, set to Null, for the
tables with 8 fields.

If you use your third table in the first SELECT clause of your Union query,
you won't need to define an alias for this field; you'll have something
like:

SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9
FROM Table3
UNION
SELECT F1, F2, F3, F4, F5, F6, F7, F8, Null
FROM Table1
UNION ...

If you have one of the 8-field tables in the first SELECT clause, you just
alias the Null entry to the 9th fieldname in the first SELECT clause; you'll
have something like:

SELECT F1, F2, F3, F4, F5, F6, F7, F8, Null As F9
FROM Table1
UNION
SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9
FROM Table3
UNION ...

Note that UNION will eliminate duplicate records from the final result. If
you have duplicates and need to retain them, you must use UNION ALL

HTH,

Rob

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.

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


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.

:

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

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!

:

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
sheet.

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











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.



:

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



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.



:

 

[ftwguy]

Evi:

I wrote the SQL for the UNION ALL, but not sure how it will help me generate
my report. The Union Query only shows FIELDS from the very first table in
the SQL statement. The remaining data from the other tables just falls under
those...so, the 8th field of table 3 falls under the 8th field of table 1
etc. I dont see how I can use it that way. My report would never know what
to look for.

I was HOPING all my fields would be across the top, and the data would fall
under it according to product, customer etc and the customer would basically
be unioned, merged together based on those IDs I wrote about like HOU6534 in
a row and all their product quanties nicely laid out left to right in the
table under the proper product name, quantity etc so I could insert ina
report.

Oh now sure if I'll get a bonus for figuring this out or get fired for not
being able to generate this critical sales report.

Well, well, I've learnt something else new, Rob. So that's how its done!
And ftwguy, don't worry about the lack of linking. The union query works
fine on unlinked tables and queries so long as they follow the Union Rules.
In fact, you may be better off keeping them unlinked with the database
'structure' which you have inherited.

If by any chance, the extra field in the 9th table is a number one, which
you need to calculate, then you could avoid Nulls by adding a dummy field to
the queries based on the other tables
In your query grid type
Dummy:0

Now those queries will have a ninth field which says 0 in it.

Similarly, if it's a Yes/No field, then your dummy field can say 0 (if you
want it to say No) or -1 if you want it to say Yes)

Don't forget, your union query will not be based directly on the tables but
on the queries which are based on the tables (or even on the queries which
are based on the queries which are based on the tables.

Your boss had better give you a big fat bonus if you solve this one,
ftwguy - talk about making bricks without straw!
Evi

You just add the additional field as a constant entry, set to Null, for the
tables with 8 fields.

If you use your third table in the first SELECT clause of your Union query,
you won't need to define an alias for this field; you'll have something
like:

SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9
FROM Table3
UNION
SELECT F1, F2, F3, F4, F5, F6, F7, F8, Null
FROM Table1
UNION ...

If you have one of the 8-field tables in the first SELECT clause, you just
alias the Null entry to the 9th fieldname in the first SELECT clause; you'll
have something like:

SELECT F1, F2, F3, F4, F5, F6, F7, F8, Null As F9
FROM Table1
UNION
SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9
FROM Table3
UNION ...

Note that UNION will eliminate duplicate records from the final result. If
you have duplicates and need to retain them, you must use UNION ALL

HTH,

Rob

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.

:

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


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.

:

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

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!

:

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
sheet.

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

 

[Evi]

I really can't picture the results you are talking about. Are you expecting
the fields in the query to be laid out as they would be in your report while
you click Autoreport to produce the results?
You say the union query 'only shows fields from the first table'.
I presume you mean that it shows the fiield *names* used in the first table.
It will show the *data* from all the tables. Field names ought to be totally
irrelevant. The labels in your reports can be changed to say whatever you
like.
Are you saying that the field names are themselves the data? (growing
horror). If yes, (and you certainly did not make that clear) then there is
no hope for your report and the person who ought to be fired is the person
who first designed it.
You say that you wanted your Fields to be *across the top* with the data
underneath.
When you say 'fields', do you actually mean data and vice versa?
A field would be Customer Name. Data would be Bill Smith.
Do you mean that you want a multicolumn report with all customers' names
listed across the top of the page and the products they buy underneath each
name?
If yes, then you need to think about turning your report into a multicolumn
report, grouped by customer name.
If you mean that you want a list of products down the left side of the
report and a list of customers across the top of the page and say totals of
what they spent in the middle, then you will have to turn your union query
into a Crosstab query.

To 'merge the customers', you will group them in the report according to
customerID using the Sorting/Grouping box and choosing to have a
Header/Footer.Put all the controls/fields about the customer himself
(name/ID/address etc) in the Group Header) so that you will have a customer
heading and under that name & ID the products which that custome bought. Or
you can take the same query and group a second report by the Product's
unique field so that the Product details are in the Group Header and the
customers who bought the product are listed in a downward list below that.
Please clarify what you want.

You may have to break newsgroup law and type a sample of what you want to
see, translating any confusing words into things easily understood ones. (If
you don't use Rich Text, then your layout will be lost).

Evi

Evi:

I wrote the SQL for the UNION ALL, but not sure how it will help me generate
my report. The Union Query only shows FIELDS from the very first table in
the SQL statement. The remaining data from the other tables just falls under
those...so, the 8th field of table 3 falls under the 8th field of table 1
etc. I dont see how I can use it that way. My report would never know what
to look for.

I was HOPING all my fields would be across the top, and the data would fall
under it according to product, customer etc and the customer would basically
be unioned, merged together based on those IDs I wrote about like HOU6534 in
a row and all their product quanties nicely laid out left to right in the
table under the proper product name, quantity etc so I could insert ina
report.

Oh now sure if I'll get a bonus for figuring this out or get fired for not
being able to generate this critical sales report.

Well, well, I've learnt something else new, Rob. So that's how its done!
And ftwguy, don't worry about the lack of linking. The union query works
fine on unlinked tables and queries so long as they follow the Union Rules.
In fact, you may be better off keeping them unlinked with the database
'structure' which you have inherited.

If by any chance, the extra field in the 9th table is a number one, which
you need to calculate, then you could avoid Nulls by adding a dummy field to
the queries based on the other tables
In your query grid type
Dummy:0

Now those queries will have a ninth field which says 0 in it.

Similarly, if it's a Yes/No field, then your dummy field can say 0 (if you
want it to say No) or -1 if you want it to say Yes)

Don't forget, your union query will not be based directly on the tables but
on the queries which are based on the tables (or even on the queries which
are based on the queries which are based on the tables.

Your boss had better give you a big fat bonus if you solve this one,
ftwguy - talk about making bricks without straw!
Evi

You just add the additional field as a constant entry, set to Null,

for
the

tables with 8 fields.

If you use your third table in the first SELECT clause of your Union query,
you won't need to define an alias for this field; you'll have something
like:

SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9
FROM Table3
UNION
SELECT F1, F2, F3, F4, F5, F6, F7, F8, Null
FROM Table1
UNION ...

If you have one of the 8-field tables in the first SELECT clause, you just
alias the Null entry to the 9th fieldname in the first SELECT clause; you'll
have something like:

SELECT F1, F2, F3, F4, F5, F6, F7, F8, Null As F9
FROM Table1
UNION
SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9
FROM Table3
UNION ...

Note that UNION will eliminate duplicate records from the final

result.
If

you have duplicates and need to retain them, you must use UNION ALL

HTH,

Rob

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.

:

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


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.

:

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

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!

:

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
sheet.

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]

Evi:

I truly appreciate all your due diligence. Today I waved the "white flag of
surrender" and simply told the boss that this problem is above my knowledge
and experience to fix. He is going to call a local MS Certified Database
Administrator to come in to insure we have this critical report by April. I
hope this person agrees. I've seen this person's resume' and very
impressive.

I believe this gentleman will see how horrible this database is, and
possibly talk them into a complete redesign as a functional relational
database.

 

[Evi]

You have all my sympathy. I wonder if you'll find out how much this chap is
going to get paid to fix the db


Evi