If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Subform comparison of 2 tables excluding non zero value
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!!!!! |
#2
|
|||
|
|||
Subform comparison of 2 tables excluding non zero value
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... -- NTC "dboollu" wrote: 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!!!!! |
#3
|
|||
|
|||
Subform comparison of 2 tables excluding non zero value
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?? "NetworkTrade" wrote: 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... -- NTC "dboollu" wrote: 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!!!!! |
#4
|
|||
|
|||
Subform comparison of 2 tables excluding non zero value
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?? "dboollu" wrote: 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?? "NetworkTrade" wrote: 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... -- NTC "dboollu" wrote: 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!!!!! |
#5
|
|||
|
|||
Subform comparison of 2 tables excluding non zero value
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. -- NTC "dboollu" wrote: 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?? "NetworkTrade" wrote: 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... -- NTC "dboollu" wrote: 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!!!!! |
#6
|
|||
|
|||
Subform comparison of 2 tables excluding non zero value
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" wrote: 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. -- NTC "dboollu" wrote: 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?? "NetworkTrade" wrote: 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... -- NTC "dboollu" wrote: 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!!!!! |
#7
|
|||
|
|||
Subform comparison of 2 tables excluding non zero value
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... -- NTC "dboollu" wrote: 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!!!!! |
Thread Tools | |
Display Modes | |
|
|