Query designing

[Limiting fields in queries]

Suppose i have 46 tables , now when i want to, run a query it ask me, which
table i need from the over all 46 tables, and once i have selected the tables
it ask for fields, now the quetion is i want access to only ask for those
fields in the dropdown menu in which some data has been fed in, fields
without any data should not appear in the option/dropdown menu

 

[Allen Browne]

No. You might design spreadsheets like that, but it is the wrong design for
a database.

If you have 46 tables, and many of them have with similar fields, that is
not a normalized design.

If you have repeating fields in a table (such as columns for Jan, Feb, ...,
or Employee1, Employee2, ...), i.e. columns where a user might want to
select which one, then that's not a normalized design either.

Consequently, the database is not set up to query like that.

Grasping the concept of what fields to store in what tables to get a
normalized design is not something you can achieve in 5 minutes. If you want
to follow it through, here's a starting point:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Limiting fields in queries" <Limiting fields in
(e-mail address removed)> wrote in message
news:[email protected]...

 

[John Spencer]

Well you would need a query to tell you which fields have data.

What you want could be done with VBA.

The basic query to find which fields have data in a specific table would be

SELECT Count(FieldA) as CountFieldA
, Count(FieldB) as CountFieldB
, Count(FieldC) as CountFieldC
FROM SomeTable


Any CountField? that was zero would need to be excluded when you built
the list of fields.

How good are your VBA skills?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Limiting fields in queries]

Thanks but John Spencer says this can be done with VBA, which is a language,
any ways i will be checking the website you mentioned, it might tun out to be
helpful, any ways thanks for taking time out for me

 

[Limiting fields in queries]

John thanks for your reply, i knew it could be done through VBA, but i was
looking for some way, in which i can do it with out involving in programming,
however if you can guide me plz email me some websites from where i can
download some notes to learn VB.

Regards

 

[John W. Vinson]

John thanks for your reply, i knew it could be done through VBA, but i was
looking for some way, in which i can do it with out involving in programming,
however if you can guide me plz email me some websites from where i can
download some notes to learn VB.

You have three choices:

1. Use some fairly complex VBA code.
2. Use some REALLY complex queries.
3. Do as Allen suggested and correct the currently *grossly incorrect*
structure of your database.

If you work *with* Access - by using properly normalized table structures -
instead of struggling *against* it - coding complex get-arounds - you'll find
it much much easier.

John W. Vinson [MVP]