Query in Access that automatically includes new fields

[Guest]

How can I make a query in Access that will automatically include newly added
fields in the SQL query statements? I want to be able to add a new field and
then open the query to see that it has found the new field and searched it
for a specific value.

In my case the field would be a checklist for whether or not computers have
received their weekly updates. I'd like to make the query automatically add
the new fields and search for computers that still need to be udpated.

Thanks for any and all help!

 

[Rick Brandt]

How can I make a query in Access that will automatically include
newly added fields in the SQL query statements? I want to be able to
add a new field and then open the query to see that it has found the
new field and searched it for a specific value.

In my case the field would be a checklist for whether or not
computers have received their weekly updates. I'd like to make the
query automatically add the new fields and search for computers that
still need to be udpated.

Thanks for any and all help!

Instead of pulling individual fields into the query pull down the top choice
of "TableName.*"

This will result in SQL of...

SELECT * FROM TableName

 

[Guest]

I've done that, so maybe my question wasn't correctly stated.

What I need is to be able to search all the fields in the "Updates" table
for a No value and if any of the fields have a No value, I want to include it
in the query results. I can do it manually by saying

WHERE (((Updates.[1]=No) OR (Updates.[2]=No) ... ));

but I was trying to find out a way to do this without having to type out
another bit of code whenever there is a new update added to the table.

 

[Rick Brandt]

I've done that, so maybe my question wasn't correctly stated.

What I need is to be able to search all the fields in the "Updates"
table for a No value and if any of the fields have a No value, I want
to include it in the query results. I can do it manually by saying

WHERE (((Updates.[1]=No) OR (Updates.[2]=No) ... ));

but I was trying to find out a way to do this without having to type
out another bit of code whenever there is a new update added to the
table.

Nope. You could dynamically build the query in code I suppose, but a table
that regularly has fields added to it points to a design flaw. These should
likely be rows in a new table rather than new fields.

 

[Guest]

Ah, I see. Thanks for the advice.

So, if I'm following correctly, I should make the updates records (rows) and
set each computer as a field (column) in that table? How would this affect
the relationship of the Updates and Computers tables (where the Computers
table has general information and an identifying Asset Tag Number)?

I've done that, so maybe my question wasn't correctly stated.

What I need is to be able to search all the fields in the "Updates"
table for a No value and if any of the fields have a No value, I want
to include it in the query results. I can do it manually by saying

WHERE (((Updates.[1]=No) OR (Updates.[2]=No) ... ));

but I was trying to find out a way to do this without having to type
out another bit of code whenever there is a new update added to the
table.

Nope. You could dynamically build the query in code I suppose, but a table
that regularly has fields added to it points to a design flaw. These should
likely be rows in a new table rather than new fields.

 

[Van T. Dinh]

SELECT [YourTable].*
FROM [YourTable]

will automatically include new Fields.

OTOH, the need to regularly adding new Fields indicates that your database
is not correctly structured. In a deployed database application, you don't
add new Fields (except for new requirements), since Tables are the basis of
other objects and adding new Fields means that you need to modify Forms /
Reports / etc ...

You database should be structured so that you can add Records to store data
for your weekly updates and not adding Fields.

 

[Guest]

Thanks for your reply. I understand what you are saying, but should I really
be making a field for each computer? I guess it doesn't really matter, it
will be up to the Forms and Reports to organize the data in an orgranized
fashion.

SELECT [YourTable].*
FROM [YourTable]

will automatically include new Fields.

OTOH, the need to regularly adding new Fields indicates that your database
is not correctly structured. In a deployed database application, you don't
add new Fields (except for new requirements), since Tables are the basis of
other objects and adding new Fields means that you need to modify Forms /
Reports / etc ...

You database should be structured so that you can add Records to store data
for your weekly updates and not adding Fields.

--
HTH
Van T. Dinh
MVP (Access)

How can I make a query in Access that will automatically include newly added
fields in the SQL query statements? I want to be able to add a new field and
then open the query to see that it has found the new field and searched it
for a specific value.

In my case the field would be a checklist for whether or not computers have
received their weekly updates. I'd like to make the query automatically add
the new fields and search for computers that still need to be udpated.

Thanks for any and all help!

 

[Guest]

To answer my own question (partly):

John Vinson said in another post:
"In a relational database, Fields are expensive,
records are cheap; you should certainly NOT store data (dates) in
field names! A tall-thin table is the ticket..."

So I suppose I will have to find a way to do this with a similar structure
to what I have now... In case anyone else is interested, I think I will use
this format:

ASSET # / Update Name / Completed (Y/N)

Now I will have multiple records for each computer, which is OK, but leads
to my next problem. How can I add several records at once (one for each
Asset # with the same Update Name)?

Thanks for your reply. I understand what you are saying, but should I really
be making a field for each computer? I guess it doesn't really matter, it
will be up to the Forms and Reports to organize the data in an orgranized
fashion.

SELECT [YourTable].*
FROM [YourTable]

will automatically include new Fields.

OTOH, the need to regularly adding new Fields indicates that your database
is not correctly structured. In a deployed database application, you don't
add new Fields (except for new requirements), since Tables are the basis of
other objects and adding new Fields means that you need to modify Forms /
Reports / etc ...

You database should be structured so that you can add Records to store data
for your weekly updates and not adding Fields.

--
HTH
Van T. Dinh
MVP (Access)

How can I make a query in Access that will automatically include newly added
fields in the SQL query statements? I want to be able to add a new field and
then open the query to see that it has found the new field and searched it
for a specific value.

In my case the field would be a checklist for whether or not computers have
received their weekly updates. I'd like to make the query automatically add
the new fields and search for computers that still need to be udpated.

Thanks for any and all help!

 

[Guest]

Hi Aaron,

I think you are definitely on the right track getting to where the other
posters were suggesting.

At the very least, You would probably want to set up tables for your Assets
(containing Asset ID and other Asset related data), your Updates (Update ID,
Update Desc, any other important update info).

From there, you have some options on how to record the updates. You would
probably want to have a table called tblUpdates, or something like that.
That table would, at a minimum, record the Asset ID and the Update ID. You
could record only those two fields, and just know that if an Asset ID does
not show up for a particular UpdateID that it still has not been implemented.
Or, as you suggested, you could store both of those fields, plus a third
field to mark when an update has been completed. In that case, each time you
have a new update you would use an append query to initially add all of the
Asset ID records with the new Update ID.

The first option is more efficient to some degree, but the one that works
out best for you will likely depend on how you will be entering the data. If
you don't want to do much form customization (such as using a list box to
allow a user to select all Asset ID's that were completed for a particular
update and using code to append those to the update table), the second method
may be easier for you because it would allow you to just run the single
append query initially and then later the person entering the data could just
check the box directly.

In either case, your data would be much more normalized than before.

Once you start normalizing your data in this way, you will find that dealing
with forms, reports, queries, etc, will become much easier, and your ability
to run complex queries will be much improved.

HTH, Ted Allen

To answer my own question (partly):

John Vinson said in another post:
"In a relational database, Fields are expensive,
records are cheap; you should certainly NOT store data (dates) in
field names! A tall-thin table is the ticket..."

So I suppose I will have to find a way to do this with a similar structure
to what I have now... In case anyone else is interested, I think I will use
this format:

ASSET # / Update Name / Completed (Y/N)

Now I will have multiple records for each computer, which is OK, but leads
to my next problem. How can I add several records at once (one for each
Asset # with the same Update Name)?

Thanks for your reply. I understand what you are saying, but should I really
be making a field for each computer? I guess it doesn't really matter, it
will be up to the Forms and Reports to organize the data in an orgranized
fashion.

SELECT [YourTable].*
FROM [YourTable]

will automatically include new Fields.

OTOH, the need to regularly adding new Fields indicates that your database
is not correctly structured. In a deployed database application, you don't
add new Fields (except for new requirements), since Tables are the basis of
other objects and adding new Fields means that you need to modify Forms /
Reports / etc ...

You database should be structured so that you can add Records to store data
for your weekly updates and not adding Fields.

--
HTH
Van T. Dinh
MVP (Access)




How can I make a query in Access that will automatically include newly
added
fields in the SQL query statements? I want to be able to add a new field
and
then open the query to see that it has found the new field and searched it
for a specific value.

In my case the field would be a checklist for whether or not computers
have
received their weekly updates. I'd like to make the query automatically
add
the new fields and search for computers that still need to be udpated.

Thanks for any and all help!

 

[Van T. Dinh]

Yep. I would suggest you check Access Help / your Access book about the
Many-to-Many relationship.

An Append Query would allow you to add multiple Records, one for each Asset,
for the same update.

--
HTH
Van T. Dinh
MVP (Access)

To answer my own question (partly):

John Vinson said in another post:
"In a relational database, Fields are expensive,
records are cheap; you should certainly NOT store data (dates) in
field names! A tall-thin table is the ticket..."

So I suppose I will have to find a way to do this with a similar structure
to what I have now... In case anyone else is interested, I think I will use
this format:

ASSET # / Update Name / Completed (Y/N)

Now I will have multiple records for each computer, which is OK, but leads
to my next problem. How can I add several records at once (one for each
Asset # with the same Update Name)?

Thanks for your reply. I understand what you are saying, but should I really
be making a field for each computer? I guess it doesn't really matter, it
will be up to the Forms and Reports to organize the data in an orgranized
fashion.

SELECT [YourTable].*
FROM [YourTable]

will automatically include new Fields.

OTOH, the need to regularly adding new Fields indicates that your database
is not correctly structured. In a deployed database application, you don't
add new Fields (except for new requirements), since Tables are the basis of
other objects and adding new Fields means that you need to modify Forms /
Reports / etc ...

You database should be structured so that you can add Records to store data
for your weekly updates and not adding Fields.

--
HTH
Van T. Dinh
MVP (Access)




How can I make a query in Access that will automatically include newly
added
fields in the SQL query statements? I want to be able to add a new field
and
then open the query to see that it has found the new field and searched it
for a specific value.

In my case the field would be a checklist for whether or not computers
have
received their weekly updates. I'd like to make the query automatically
add
the new fields and search for computers that still need to be udpated.

Thanks for any and all help!

 

[Guest]

Thank you Ted and Van. Your exaplanations helped greatly.

-Aaron

Yep. I would suggest you check Access Help / your Access book about the
Many-to-Many relationship.

An Append Query would allow you to add multiple Records, one for each Asset,
for the same update.

--
HTH
Van T. Dinh
MVP (Access)

To answer my own question (partly):

John Vinson said in another post:
"In a relational database, Fields are expensive,
records are cheap; you should certainly NOT store data (dates) in
field names! A tall-thin table is the ticket..."

So I suppose I will have to find a way to do this with a similar structure
to what I have now... In case anyone else is interested, I think I will use
this format:

ASSET # / Update Name / Completed (Y/N)

Now I will have multiple records for each computer, which is OK, but leads
to my next problem. How can I add several records at once (one for each
Asset # with the same Update Name)?

Thanks for your reply. I understand what you are saying, but should I really
be making a field for each computer? I guess it doesn't really matter, it
will be up to the Forms and Reports to organize the data in an orgranized
fashion.

:

SELECT [YourTable].*
FROM [YourTable]

will automatically include new Fields.

OTOH, the need to regularly adding new Fields indicates that your database
is not correctly structured. In a deployed database application, you don't
add new Fields (except for new requirements), since Tables are the basis of
other objects and adding new Fields means that you need to modify Forms /
Reports / etc ...

You database should be structured so that you can add Records to store data
for your weekly updates and not adding Fields.

--
HTH
Van T. Dinh
MVP (Access)




How can I make a query in Access that will automatically include newly
added
fields in the SQL query statements? I want to be able to add a new field
and
then open the query to see that it has found the new field and searched it
for a specific value.

In my case the field would be a checklist for whether or not computers
have
received their weekly updates. I'd like to make the query automatically
add
the new fields and search for computers that still need to be udpated.

Thanks for any and all help!