Re: Sum of numbers

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

"ftwguy" <ftwguy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:38A0E2BA-F1DA-4E58-B91A-823309228C95@xxxxxxxxxxxxxxxx
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