Re: Sum of numbers

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

You'll remember that much of your difficulty started because you weren't
allowed to re-design the database but had to work with it, as it is.
I suspect your chap will use a Make Table query at some point.
Oh to be a fly on the wall!
Evi

"ftwguy" <ftwguy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:33AE058C-F73E-4BE2-9295-8178D48DAFAA@xxxxxxxxxxxxxxxx
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
    ... "Evi" wrote: ... 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: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... I also tried a UNION ALL, ... the actual structure of the queries as they have been running fine for weeks. ...
    (microsoft.public.access.queries)
  • Re: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... which is one of the union-ed queries. ... I also tried a UNION ALL, ...
    (microsoft.public.access.queries)
  • Re: Query to search multiple identical tables
    ... You seem to think Query and Form have their own set ... So, when you edit ... UNION Queries are NOT updateable since in a union, ... >This, of course, violates the Relational Database Design ...
    (microsoft.public.access.queries)
  • Re: Query to search multiple identical tables
    ... You seem to think Query and Form have their own set of data??? ... So, when you edit data, you edit data ... UNION Queries are NOT updateable since in a union, JET engine, in ... This, of course, violates the Relational Database Design Theory since you ...
    (microsoft.public.access.queries)