Re: Sum of numbers

Well I caved in with regret as I hate being defeated. I guess I take it too
personal that I failed. I did speak with the DB designer today, gave him an
overview of all the problems. He did say the Union All query would not fix
my report problem, but some 'scripting" would do it. I did explain the huge
tables to him and he agreed that it all needs to be redone, and knew exactly
the problems I am encountering. He said he could even write me a program to
automatically inmport sales data from Excel into the Access tables, and hit
every cell with the correct data, and ignore the ones that dont need data.
Now that I will be amazed by.

Don't know his fee yet. That's something between he and the boss, but I'll
hear what it is. I just warned the boss not to be too low in the contract
price and offend the guy with that much experience, education and
certifications.

"Evi" wrote:

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
"ftwguy" <ftwguy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1CE7E509-4C1D-4314-B220-BDC289F56F07@xxxxxxxxxxxxxxxx
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" wrote:

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




"ftwguy" <ftwguy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5D36F724-B432-4D1C-B445-43E859B43A3C@xxxxxxxxxxxxxxxx
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.

"Evi" wrote:

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

"Rob Parker" <NOSPAMrobpparker@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:OicaIczeIHA.1204@xxxxxxxxxxxxxxxxxxxxxxx
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
.

Relevant Pages

  • Re: Sum of numbers
    ... Administrator to come in to insure we have this critical report by April. ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)
  • Re: Sum of numbers
    ... Administrator to come in to insure we have this critical report by April. ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)
  • Re: Sum of numbers
    ... allowed to re-design the database but had to work with it, ... "Evi" wrote: ... You say the union query 'only shows fields from the first table'. ...
    (microsoft.public.access.reports)
  • Re: Multiple WHERE conditions - Not sure about this post
    ... rather than some union or PL/SQL code.... ... Write a query to find the customers whose trail is complete ... Write a query to find customers with active non-trail ...
    (comp.databases.oracle.misc)
  • Re: Make Relationship w/UNION-joined data?
    ... You can't create relationships between a query and a table. ... with different structures (although an SQL UNION ... >fo customers. ... Please respond in the newgroup and not by email. ...
    (microsoft.public.access.tablesdbdesign)