Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to count the number of coulmns in a table or query? I have a
db with probably 30 columns in it. But, that number is subject to change. I
need the total number of columns to comple a calculation and changing it each
time we add or delete a column is not the answere I really want to hear.

Any help would greatly be appericated.
 
You can use code like:
currentdb.tabledefs("employees").Fields.Count

Adding and deleting fields/columns doesn't sound like a very efficient table
structure.
 
Is there a way to count the number of coulmns in a table or query? I have a
db with probably 30 columns in it. But, that number is subject to change. I
need the total number of columns to comple a calculation and changing it each
time we add or delete a column is not the answere I really want to hear.

Any help would greatly be appericated.
Click to expand...

If you routinely need to add and delete fields in your table...

Your table structure is almost certainly WRONG.

"Fields are expensive, records are cheap". I suspect that you are
"comitting spreadsheet" by storing data in fieldnames, in effect
embedding a one-to-many relationship within each record. If you have
28 or 32 or 29 "somethings" in each record, then a much better design
would be to have two Tables in a one to many relationship, with 28 or
32 or 29 *records* in the many side table for each record in the main
table. This will make your calculations MUCH easier since you can use
a simple Totals query to count (or sum or average or whatever) the
values in the related records.

John W. Vinson[MVP]
 
What I am doing is setting up a qualification db. The adding and deleting
columns is for changes in the requirements and should not happen but maybe
once every two or three years if that. The reasoning behind counting the
fields is when I am not there I don't have to try and fix it each time they
add or delete something.

There are a total of 6 tables in the app with 4 to 10 columns in each table.
One is a personnel table and the rest are various tasks being broken up by
the varioius areas. All tasks are identified complete with a check box.
Any suggestions on how to better utilize/build this?
 
What I am doing is setting up a qualification db. The adding and deleting
columns is for changes in the requirements and should not happen but maybe
once every two or three years if that. The reasoning behind counting the
fields is when I am not there I don't have to try and fix it each time they
add or delete something.

There are a total of 6 tables in the app with 4 to 10 columns in each table.
One is a personnel table and the rest are various tasks being broken up by
the varioius areas. All tasks are identified complete with a check box.
Any suggestions on how to better utilize/build this?
Click to expand...

Yes, there is a better way. Use Access as a relational database,
rather than as a spreadsheet!

You have a couple of perfectly normal many (employees) to many
(requirements) relationships. The proper way to do this is to use
THREE tables not two; store Requirements for a task as multiple
RECORDS rather than as multiple fields.

Employees
EmployeeID
LastName
FirstName
<biographical data>

Tasks
TaskID
TaskDescription
<other info about the task itself>

Requirements
RequirementID
RequirementDescription

TaskRequirements
TaskID <link to Tasks>
RequirementID <link to Requirements>

Assignments
EmployeeID <link to Employees>
TaskID <link to Tasks>
<information about this employee's assignment to this task>


John W. Vinson[MVP]
 
Back
Top