designing a query

[saikat_datta]

Hi sir,



i am new in access.
MY PROBLEM IS AS FOLLOWS.

I HAVE A TABLE <Table1> with 10 columns. These are primaryID, Name, Adress, Age, appeaed, dd, ss, gg, hh, yy.

Now some field may not have any values. Like For the name John the field <ss> will not hold any value. Same is for RONY as <ss> does not hold any value.

i want to design the query to fetch all except the fields primaryID, address and age for all the 12 records but with two criteria.
first - the name has to be RONY.
second - any column with null values for all the 12 entries will not be shown in result.
Ex. the field <hh> is null for all the 10 entries i have entered.

please help

regards.

[Micron]

You cannot easily choose which columns to include based on whether or not they hold any particular (or lack of a) value. To even want to do this suggests your table(s) is/are not structured properly. You can eliminate any rows where a field is NULL (does not have data), but it eliminates the entire row, not just some of the fields (as you say, column). If you are imagining this with a 'spreadsheet' bias or paradigm, it is the wrong approach. Perhaps you should post more data so we can see what you're dealing with. If you can create some fake data in a spreadsheet that looks like your table data, copy and paste it into your next post and it should simplify creating a table in your post. Otherwise please do so manually so we're not guessing at what you have.

[saikat_datta]

PrimariID	Name	Address	Age	Appeard in Job	ddrram	sdram	hdd	mouse	keyboard
1	Rony	eretetert	34	yes	1	0	1	0	0
2	Rony	eretetert	34	yes	0	0	1	0	0
3	Roxy	aabbccdd	26	yes	0	0	0	1	0
4	Shyam	dddddd	45	yes	1	0	1	0	0
5	Biny	fffffff	21	yes	0	0	0	1	0
6	Shyam	dddddd	45	yes	1	0	1	1	0

This is kind a example of my table. I want to select records for a person but only want to display the fields which will not having 0 as values . Like if select Shyam it should return

PrimariID	Name	Address	Age	Appeard in Job	ddrram	hdd	mouse
4	Shyam	dddddd	45	yes	1	1	0
6	Shyam	dddddd	45	yes	1	1	1

Is it Possible?

Regards

[Micron]

Should I assume you made a typo for record 4 (there is one field with a zero)? I cannot see how this could ever be done in one step, since as I mentioned, you cannot exclude a field based on its data or lack thereof. Perhaps someone else will correct me on that. Try this:
Go to sql view of a new query and paste this then save as "qrySomeFields2"

SELECT tblSomeFields.ID, tblSomeFields.Name, tblSomeFields.Address, tblSomeFields.Age, tblSomeFields.ddrram AS TypeQty, "ddrram" AS TypeFld FROM tblSomeFields WHERE (((tblSomeFields.Name)="Shyam") AND ((tblSomeFields.ddrram)<>0))
UNION ALL SELECT tblSomeFields.ID, tblSomeFields.Name, tblSomeFields.Address, tblSomeFields.Age, tblSomeFields.sdram AS TypeQty, "sdram" AS TypeFld FROM tblSomeFields WHERE (((tblSomeFields.Name)="Shyam") AND ((tblSomeFields.sdram)<>0))
UNION ALL SELECT tblSomeFields.ID, tblSomeFields.Name, tblSomeFields.Address, tblSomeFields.Age, tblSomeFields.hdd AS TypeQty, "hdd" AS TypeFld FROM tblSomeFields WHERE (((tblSomeFields.Name)="Shyam") AND ((tblSomeFields.hdd)<>0))
UNION ALL SELECT tblSomeFields.ID, tblSomeFields.Name, tblSomeFields.Address, tblSomeFields.Age, tblSomeFields.mouse AS TypeQty, "mouse" AS TypeFld FROM tblSomeFields WHERE (((tblSomeFields.Name)="Shyam") AND ((tblSomeFields.mouse)<>0))
UNION ALL SELECT tblSomeFields.ID, tblSomeFields.Name, tblSomeFields.Address, tblSomeFields.Age, tblSomeFields.keyboard AS TypeQty, "keyboard" AS TypeFld FROM tblSomeFields WHERE (((tblSomeFields.Name)="Shyam") AND ((tblSomeFields.keyboard)<>0));

Do the same for a new query, paste and save as "qrySomeFieldsCrossTab"

TRANSFORM Count(qrySomeFields2.[TypeQty]) AS CountOfTypeQty
SELECT qrySomeFields2.[ID], qrySomeFields2.[Name], qrySomeFields2.[Address], qrySomeFields2.[Age] 
FROM qrySomeFields2
GROUP BY qrySomeFields2.[ID], qrySomeFields2.[Name], qrySomeFields2.[Address], qrySomeFields2.Age 
PIVOT qrySomeFields2.[TypeFld];

I think this will work regardless of which fields are returned when you supply a name as criteria. However, I cannot eliminate the "mouse" column because there IS a value related to the name, using the data you supplied.

ID	Name	Address	Age	ddrram	hdd	mouse
4	Shyam	dddddd	45	1	1
6	Shyam	dddddd	45	1	1	1

[June7]

Fields cannot be dynamic in regular SELECT query object, unless you want to write VBA to modify object - a field is in the query or it isn't. CROSSTAB query is entirely different animal. It is not easy to build a form or report based on CROSSTAB because of the dynamic nature of fields. Review http://allenbrowne.com/ser-67.html#ColHead

[Micron]

True. Which is why the proposed solution puts the field names in one column with their corresponding value in another column. The crosstab can transpose the field name as a header and put the corresponding value below that field name. Not sure how well it would work beyond the limited data example provided, but the result I posted is based on it. I'm assuming that putting the field names in one column makes the variation of field names dynamic, so they are in if the initial UNION query returns them. If not, then different fields are returned as long as there are values returned using the criteria.

[June7]

Yes, I understand the approach to resolve OP issue, just wanted them to be aware of other issues they may encounter with the CROSSTAB.