Selecting only Values

[Naveen Marapaka]

Hi,



I have a table T with fields Dated( date field), s1,s2,s3,s4,s5 all number fields

on each date some fields has values and some fields are zero's(null values)

this goes on different dates.

I want a query to select only those fields to display which has values.

Pls advise.


Thanks in advance

[Naveen Marapaka]

I want a query to select only those fields to display which has values of a selected date

[June7]

Use the Query Builder. Access Help has guidelines on building queries with filter parameters.

Criteria can be input through popup prompts or by reference to controls on a form. I prefer the latter. The query statement would be like:

SELECT * FROM tablename WHERE [datefield] = #" & Forms!formname!textboxname & "#";

[Naveen Marapaka]

Hi,
this is resulting in displaying all fields of that date selected.

My question is to create query which should display only fields in result which has value greater than 0.

Example Row1 in the table has values : 01/09/2013 20 10 0 0 100

What should be the Select query to display only fields 20 10 100 excluding fields which has 0 value, where date = 01/09/2013

[June7]

This is very basic Access functionality. You should get familiar with building queries with the query designer.

SELECT * FROM tablename WHERE [datefield] = #1/9/2013# AND [fieldname] <> 0;

Include as many criteria as you want. Use the query builder design grid.

[pervez]

Use the criteria in query with "IsNull" or "Isnotnull" hence you can find the required results

[Naveen Marapaka]

Hi,

Data : 01/09/2013 20 10 0 0 100
Data : 02/09/2013 0 0 10 20 0

as example below 2 queries i applied the same given query

1. SELECT * FROM tablename WHERE Dated = #1/9/2013# AND s1 <> 0 and s2<>0 and s3 <>0 and s4<>0 and s5<>0;


2. 1. SELECT * FROM tablename WHERE Dated = #2/9/2013# AND s1 <> 0 and s2<>0 and s3 <>0 and s4<>0 and s5<>0;




it is displaying zero records when i run queries

My point is Select * itself -we are defining to display all the fields applying condition Dated = #1/9/2013 and ss1 <> 0 and s2<>0 and s3 <>0 and s4<>0 and s5<>0

i dont want to select all the fields to display, only display those values which are greater than zero


when i run each query result should be

for query1 : 20 10 100

Fields S3 , S4 should be skipped



For query2 : 10 20

Fields S1,S2,S5 should be skipped


how can i make a query to select only those fields which has values of a given date.

[June7]

Apparently no records meet the criteria which requires that every s# field to not be 0. If even only one of the fields has 0, the record will not be retrieved. I don't understand your data. Having tables with similar name fields indicates a non-normalized structure. That might be the cause of difficulty.

If you don't want to display certain fields then don't use the wildcard, specify the field names. But what is the issue with displaying all the fields?

If you don't need all 5 fields as criteria, then don't use all 5.

[Naveen Marapaka]

The given data is an example of 2 dates only, i have more an year data,

and even number of fields are also more in my actual table , S1----------S11.

So its impossible to know which field has 0 value of which date.

so selecting all fields will display unnecessary fields which have 0 values also in output which i want to avoid.

Is there any possibility to make query ?

[June7]

Multiple similar name fields indicates data is not normalized. This complicates sort and filter. Proper data structure is essential for effective data management and analysis.

Not really understanding filter rule. Do you want all records for specified data if there is a 0 in any of the fields or do you want to exclude record if there is a 0 in any field?

[Naveen Marapaka]

I will clarify

I mentioned Fields name s1, s2,s3,s4,s5 because for better understanding of this query and only to get the logic, any how fields names are different to one another over all.


I just want to exclude fields in output display which has 0 value of a selected date.
what should be my query to get it.

like
Select ( ????? ) from table T where Dated = #1/9/2013#;

???? - what to mention here so that it will check table fields and select only fields which has values of given date.



if we mention * it selected all fields which i dont want

if we mention * and where condition s1 <> 0 and s2<>0 and s3 <>0 and s4<>0 and s5<>0 it will retrieve records only if this condition is satisfied but this is not corrrect criteria for my question,


To be more clear.

let say data is S1 is 0, S2 is 20, S3 is 0, S4 is 50, S5 is 0 of date 01/09/2013

so my query should be select S2,S4 from T where date =#01/09/2013#;

then result will be 20 50


let say data is S1 is 100, S2 is 20, S3 is 0, S4 is 0, S5 is 50 of date 01/09/2013

so my query should be select S1,S2,S5 from T where date =#01/09/2013#;

result will be 100 20 50


in this way system should check which field has value and select only those fields in my query.
Select ( ????? ) from table T where Dated = #1/9/2013#;

i dont want to enter manually fields in that bracket (????) everytime in the query which field has value greater than zero.

[June7]

Query cannot dynamically select field based on whether or not it has data. Either field is in the query or it isn't. Records are selected based on data, not fields.