Subform comparison of 2 tables excluding non zero value

[dboollu]

I am creating a subform (Access 2003/NT) in which the user will type in a
Melt# (contained in the tblChemicalResults). I need to create a side by side
comparison of the chemical requirements from the tblChemicalRequirements to
the actual results contained in the tblChemicalResults, but I need it to
exclude any non zero values from both tables. Each table contains all of the
possible 14 different metal elements that could possibly go into making a
particular part. Obviously, not all 14 go into every part, so how do I
display only the elements that have a non zero value while also displaying
the corresponding Melt# (or possibly #'s)?

Sadly, I have been trying to figure this our for far more time than I am
willing to admit. I am just getting back into this after some years, so
please have patients in my foolishness as I am sure this is some pretty easy
and basic stuff.

Any and all help is greatly appreciated!!!!!

 

[NetworkTrade]

I don't completely understand your request. But to avoid the nonzero records
- means that you only want the zeros: that is easy; source the form on a
query, not directly on the table, and in the criteria of the query put 0

that query then should return only records that have a 0 value in the
column....

if you actually want nulls; which are different than 0s, search for null
info on this site...it is a different approach...

 

[dboollu]

Whoops!! I meant that I DIDN'T want any field not containing a value to be
returned to the form. ie/if after the chemical analysis was done and a
particular element was not present in the chemical make up of the part it
(the field) would not be displayed in the form. Does that make any sense?
Or did I phrase it wrong??

 

[dboollu]

Maybe this will clarify a little better...

My tables are structured is as folows:

tblChemicalResults tblChemicalRequirements
Melt# Melt#
AlloySpec
SI SI
FE FE
CU CU
MN MN
CR CR
NI NI
ZN ZN
SN SN
TI TI
BE BE
CO CO
Other Other
Total Total

I don't have any real data at this point, but the data is simply numbers
generated during the process of a machine melting down (where the Melt#
originates) the specified part to perform a chemical analysis of what metal
elements the part contains to be sure it contains the correct contents for
the part. The resulting data would be stored in the ChemicalResults table.
I need to do a side by side comparison of that data in a form (leaving out
any fields that do not contain data returned from the chemical analyse
machine) with the elements contained in the ChemicalRequirements table (which
is specified by the customer).

Does that make any more sense??

 

[NetworkTrade]

I don't thing the advice changes. I believe the essence of your issues is
avoiding nulls or possibly 0s; rather than finding them.

Pre-Access2007 a table field with number format will default a 0 value
unless you turn that off. If your table is full of 0s then to avoid them you
easily put in >0 as the query criteria.

If it is blank/null then work with the IsNotNull ; search null in the query
area of this site.

But in the end you are working with a query(s) that is set up to avoid
returning records that have no value - this is very common.

 

[dboollu]

Yes, I believe you are correct about the avoiding nulls/zeros issue, and
thank you for the advice! I haven't done db work in a few years, so I'm
trying to wrap my head around it again.

My other issue is in trying to figure out the best avenue of generating the
required information on my form. I first thought I would need to create a
subform with some type of filter to avoid the nulls/zeros (goes with your
advice), but how would I display the side-by-side results in the subform that
I require. Then it was suggested to me that I use a crosstab (pivot) query.
I looked into this and thought it would display my data beautifully on my
form and then my report, but can't really figure out how to programatically
avoid the requirement of a crosstab query to 'calculate' something. I'm
pretty sure you can do it as I've seen examples of it, but don't really know
the parameter/expression (not sure which) to use to avoid having to set some
type of calculation in the required "values" output field. Or really how to
write the code to to generate this query.

Any ideas on which would be better for display on my form/report?

 

[NetworkTrade]

a form or report can only display; so your issue is query.

if you can structure a query with joining tables that returns the correct
result that is ideal.

many times for side-by-side; the solution is two separate queries -
presuming there is a logical unique key field matching in both. then in the
form/report you begin with the primary query as its record source and insert
the subform/report - - the wizard will walk you thru linking the two together
on the unique key field...

as to crosstabs - they are what they are....if it works for what you need
then just try it and see...