Forms and Union Queries

[Mariano]

Does anybody know if there is a limit to the amount of queries in a union
query that a form will display?

Here is what I am seeing-
I am trying to do a quick preview of records from a table before I insert
them into a new table to store the results. This is an estimating system for
a transportation manufacturer. The user enters a series of specs, each group
of components have their own entity tables to select the proper item in that
group, the queries pull those related parts from the master parts list and
dumps it into an "estimate" table to store the results of that estimate.
That works fine, but now they want a way to preview the results before its
entered as an estimate. So I figured if I made a form bound to a union query
of all my selection queries I should be good to go. The union query works,
all the records are being displayed in the form, but my subtotal calculation
in the form footer wasnt working. I figured there was a problem with one of
my queries so I went back to the Union query and added each query one by one
to see where the "bad query" was. (Again, this is just to preview and this
is not the way the data is getting dumped to the estimate table) I get up to
22 queries in my Union query with no problems, but once I enter the 23rd the
subtotal in the Form footer stops working and returns #Error. It definitely
isnt a problem with that query because I tried it with different ones, it
just looks like Access is overwhelmed and stops calculating.

Does anyone have any ideas?

 

[John W. Vinson]

Does anybody know if there is a limit to the amount of queries in a union
query that a form will display?

Here is what I am seeing-
I am trying to do a quick preview of records from a table before I insert
them into a new table to store the results. This is an estimating system for
a transportation manufacturer. The user enters a series of specs, each group
of components have their own entity tables to select the proper item in that
group, the queries pull those related parts from the master parts list and
dumps it into an "estimate" table to store the results of that estimate.
That works fine, but now they want a way to preview the results before its
entered as an estimate. So I figured if I made a form bound to a union query
of all my selection queries I should be good to go. The union query works,
all the records are being displayed in the form, but my subtotal calculation
in the form footer wasnt working. I figured there was a problem with one of
my queries so I went back to the Union query and added each query one by one
to see where the "bad query" was. (Again, this is just to preview and this
is not the way the data is getting dumped to the estimate table) I get up to
22 queries in my Union query with no problems, but once I enter the 23rd the
subtotal in the Form footer stops working and returns #Error. It definitely
isnt a problem with that query because I tried it with different ones, it
just looks like Access is overwhelmed and stops calculating.

Does anyone have any ideas?

There are limits on the size of queries, and I'd suggest you are
hitting that limit.

If you're stuck with this badly denormalized design (storing "the
same" data in multiple tables is generally A Bad Idea), then I'd
suggest doing the totalling *first* and then summing the results. A
VBA loop calculating the sum (using a totals query or DSum() for
example) and adding the sums in the loop might be another possibility.

John W. Vinson [MVP]

 

[Mariano]

Thanks John.
I am not sure I explained how my tables are set up well. The same data is
not stored in multiple tables. All of the select queries in this union query
are using the same table to pull from, it is just that each component group
has individual entity tables to create the relationship.
For example-
The user enters that the speed of the machine is 300fpm in the entry table
(entry.speed). The component named buffers is chosen by speed, i have all my
buffers in a table called buffers that simply has 2 fields- buffers.speed and
buffers.partid. These two tables are joined using the speed field. I also
have a masterparts list with every item in every component that contains
pricing, vendor information, partid,etc. Each component table is joined to
the masterparts list using the partid fields. So my select query for buffers
selects from the master partslist where entry.speed=buffer.speed and
buffer.partid=masterparts.partid.
I do seperate queries for each component group since they are selected based

 

[John W. Vinson]

Thanks John.
I am not sure I explained how my tables are set up well. The same data is
not stored in multiple tables. All of the select queries in this union query
are using the same table to pull from, it is just that each component group
has individual entity tables to create the relationship.
For example-
The user enters that the speed of the machine is 300fpm in the entry table
(entry.speed). The component named buffers is chosen by speed, i have all my
buffers in a table called buffers that simply has 2 fields- buffers.speed and
buffers.partid. These two tables are joined using the speed field. I also
have a masterparts list with every item in every component that contains
pricing, vendor information, partid,etc. Each component table is joined to
the masterparts list using the partid fields. So my select query for buffers
selects from the master partslist where entry.speed=buffer.speed and
buffer.partid=masterparts.partid.
I do seperate queries for each component group since they are selected based
on different criteria.
Does this make sense? Is this a bad way of doing it?

ok... that clarifies things for me. You're (correctly) doing
"Subclassing" - you have not just multiple Masterparts, but multiple
*kinds* of Masterparts, each with different attributes.

Given that, I think you're doing it right - I can't see any easy way
to create a single query if the criteria come from all these related
tables. A Query left-joining each of the component tables to
Masterparts, with criteria including OR IS NULL for those cases where
the criterion is intended for another component, would be even more
complicated than the UNION query and probably less efficient as well.

Tough one!

John W. Vinson [MVP]

 

[Will G]

Hey John,
i have couple of problems that i think you could help me with....
1. i have that problem of "badly denormalized design" now can you brief the
code that i will be using to loose this problem. i think you started with
"totalling *first* and then summing the results." Now, let me tell you that i
am totally new to this and please be patient with me.
2. somehow, this line of code is giving me a Type mismatch error
DoCmd.OpenReport strProductID, acViewPreview, , "[ProductID]='" &
Me![ProductID] & "'" And "[OrderNo] = " & Me![OrderNo]
and all i want to do is have those two field as a criteria for my report
display ...
i will appreciate if you could help me... thanks

 

[John W. Vinson]

Hey John,
i have couple of problems that i think you could help me with....
1. i have that problem of "badly denormalized design" now can you brief the
code that i will be using to loose this problem. i think you started with
"totalling *first* and then summing the results." Now, let me tell you that i
am totally new to this and please be patient with me.
2. somehow, this line of code is giving me a Type mismatch error
DoCmd.OpenReport strProductID, acViewPreview, , "[ProductID]='" &
Me![ProductID] & "'" And "[OrderNo] = " & Me![OrderNo]
and all i want to do is have those two field as a criteria for my report
display ...
i will appreciate if you could help me... thanks

Will, I think you're attributing knowledge that I don't have. You
posted this message as a reply to an unrelated thread, and I don't
know the structure of your tables, and have no idea whether the advice
I was giving Mariano is relevant to your case at all.

The error message you're getting *MAY* have to do with the quotes. If
ProductID is a Number type field then you do not need singlequotes as
delimiters; try

DoCmd.OpenReport strProductID, acViewPreview, , "[ProductID]=" &
Me![ProductID] & " And [OrderNo] = " & Me![OrderNo]

This will piece together a criteria string like

[ProductID] = 31 AND [OrderNo] = 446

If one of the fields is of Text type then you do need single quotes
around it; the ' character can be just included in your string
constant. That is, if ProductID is of Text type, your string should be

DoCmd.OpenReport strProductID, acViewPreview, , "[ProductID]='" &
Me![ProductID] & "' And [OrderNo] = " & Me![OrderNo]

This will assemble the WhereCondition from its components:

[ProductID] = '
<the product ID value from the form>
' And [OrderNo] =
<the OrderNo from the form>

John W. Vinson [MVP]

 

[Will G]

Hey John,
thanks for the help and ur suggestion to the second problem worked
perfectly, it seems like i had an extra quote somewhere. and yea, ProductID
is a string. now, the cenario to the first problem is that i have this big
table containing field of the same data. and i like to come up with a report
that contain those data. example...fields: quilt1, quilt2 and thier Vendor,
i like to come with a report that in one line displays both quilts, sorted by
vendor. now, this might sound empty man, but like i said this is the
beginning for me with this and i hope you could help me. thanks again.
--
need help

Hey John,
i have couple of problems that i think you could help me with....
1. i have that problem of "badly denormalized design" now can you brief the
code that i will be using to loose this problem. i think you started with
"totalling *first* and then summing the results." Now, let me tell you that i
am totally new to this and please be patient with me.
2. somehow, this line of code is giving me a Type mismatch error
DoCmd.OpenReport strProductID, acViewPreview, , "[ProductID]='" &
Me![ProductID] & "'" And "[OrderNo] = " & Me![OrderNo]
and all i want to do is have those two field as a criteria for my report
display ...
i will appreciate if you could help me... thanks

Will, I think you're attributing knowledge that I don't have. You
posted this message as a reply to an unrelated thread, and I don't
know the structure of your tables, and have no idea whether the advice
I was giving Mariano is relevant to your case at all.

The error message you're getting *MAY* have to do with the quotes. If
ProductID is a Number type field then you do not need singlequotes as
delimiters; try

DoCmd.OpenReport strProductID, acViewPreview, , "[ProductID]=" &
Me![ProductID] & " And [OrderNo] = " & Me![OrderNo]

This will piece together a criteria string like

[ProductID] = 31 AND [OrderNo] = 446

If one of the fields is of Text type then you do need single quotes
around it; the ' character can be just included in your string
constant. That is, if ProductID is of Text type, your string should be

DoCmd.OpenReport strProductID, acViewPreview, , "[ProductID]='" &
Me![ProductID] & "' And [OrderNo] = " & Me![OrderNo]

This will assemble the WhereCondition from its components:

[ProductID] = '
<the product ID value from the form>
' And [OrderNo] =
<the OrderNo from the form>

John W. Vinson [MVP]

 

[John W. Vinson]

Hey John,
thanks for the help and ur suggestion to the second problem worked
perfectly, it seems like i had an extra quote somewhere. and yea, ProductID
is a string. now, the cenario to the first problem is that i have this big
table containing field of the same data. and i like to come up with a report
that contain those data. example...fields: quilt1, quilt2 and thier Vendor,
i like to come with a report that in one line displays both quilts, sorted by
vendor. now, this might sound empty man, but like i said this is the
beginning for me with this and i hope you could help me. thanks again.

If you have fields named Quilt1 and Quilt2... you are almost certainly
"committing spreadsheet". Repeating fields like this are *incorrect*
table design! If you have a one (something, I have no idea what your
table represents) to Many (quilts) relationship, you need *two tables*
in a one to many relationship.

Perhaps you could post a description of your table in the format:

Tablename
Fieldname1; datatype; description
Fieldname2; datatype; description


John W. Vinson [MVP]

 

[Will G]

yes John,
my table looks like this:
bedID quilt1 quilt2 quilt3 spring uphfill1 uphfill2 uphfill3 uphfill3

and this table was originally made before i started working with this
database and i am trying to correct couple of the problems that it has. the
data is redundant and i like to create one table for each part. can you brief
this for me on how to go about and how would i link these tables to BED table
and if i was to create a form that is indended to input the information the
way it's shown on that table, how can all this will go into one....sorry man,
i hope you understand what i am looking for....and thanks again and sorry for
interupting your session with the other guy.

 

[John W. Vinson]

yes John,
my table looks like this:
bedID quilt1 quilt2 quilt3 spring uphfill1 uphfill2 uphfill3 uphfill3

and this table was originally made before i started working with this
database and i am trying to correct couple of the problems that it has. the
data is redundant and i like to create one table for each part. can you brief
this for me on how to go about and how would i link these tables to BED table
and if i was to create a form that is indended to input the information the
way it's shown on that table, how can all this will go into one....sorry man,
i hope you understand what i am looking for....and thanks again and sorry for
interupting your session with the other guy.
--

What's a uphfill? What's the real-life relationship between Beds and
Quilts? What's the real-life relationship between Beds and uphfills?
What's a "spring" in this context - e.g. what would the spring field
typically contain?

Remember: I cannot see your database. I do not know anything about
your business or what this database if for. I wish my telepathy were
working a bit better... but it's not!

All I can say is that this table structure IS WRONG, and if you go to
the effort to create a form to fill in a table of this structure,
you'll really be going down the wrong track.

You might want to read up on database design and normalization,
particularly from the Database Design 101 links on Jeff's site:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]