How do I create an Access 2007 table using VBA?

I'm writing a Access 2007 db to help with inventory control that contains a
crosstab table which pulls together a product's descriptive elements to make
a model part number for the product. The query then lists each part's serial
number below the part number in a column. I want to then take that part
number and dynamically make a table-field out of it with the serial numbers
listed below. Since this value can change each time the query is run, the
table has to be dynamic and cleared after each use. I've found that crosstab
queries don't play well with the make-table option in queries or with reports
at gen time. I thought that by leaving the query as a select query, then
programmatically creating the table fields, I could get around this
irritation. Any ideas?

David

It sounds like you are trying to dynamically create new fields named after
partnumbers. If so, that approach embeds data (partnumber) in field names,
a no-no in a relational database.

You've asked a "how" question. It all starts with the data ... please
describe the data you're working with.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"DavidB" wrote in message
...
I'm writing a Access 2007 db to help with inventory control that contains
a
crosstab table which pulls together a product's descriptive elements to
make
a model part number for the product. The query then lists each part's
serial
number below the part number in a column. I want to then take that part
number and dynamically make a table-field out of it with the serial
numbers
listed below. Since this value can change each time the query is run, the
table has to be dynamic and cleared after each use. I've found that
crosstab
queries don't play well with the make-table option in queries or with
reports
at gen time. I thought that by leaving the query as a select query, then
programmatically creating the table fields, I could get around this
irritation. Any ideas?

On Aug 21, 3:36*pm, DavidB wrote:
I'm writing a Access 2007 db to help with inventory control that contains a
crosstab table which pulls together a product's descriptive elements to make
a model part number for the product. The query then lists each part's serial
number below the part number in a column. I want to then take that part
number and dynamically make a table-field out of it with the serial numbers
listed below. Since this value can change each time the query is run, the
table has to be dynamic and cleared after each use. I've found that crosstab
queries don't play well with the make-table option in queries or with reports
at gen time. I thought that by leaving the query as a select query, then
programmatically creating the table fields, I could get around this
irritation. Any ideas?

I agree with Jeff. I spent six months working on projects that, if
built correctly (not storing facts in field names), would have taken
one day instead of six months to do... you would be making an enormous
amount of work for yourself. And the code to sort the mess out was
not trivial.

No doubt that this is a significant (but creative) challenge.
To describe the data. I have Dell computers that my company obtains from a
client and is responsible to resell. I have developed a database system that
through scanning the service tag & the client's asset tag, enables me to
track the device from the time it enters the door to our warehouse till the
time I resell it. I'm trying to develop a bill of sale that allows me to
segregate the data by the pc descriptive information (ex: GX240 BLK/[client
service tag]) so that I can list each device sold by the descriptive info in
a column. It seems to me that if I can list each sold item (which can be from
one model to ten different models, based on what is available to sell) under
the descriptive info about that item, it would be most efficient. This
information will also be stored in an "Items Sold" table that is indexed by
the type of purchaser (reseller, commercial, or employee). The data will be
used to generate a report (dynamically) and then the table (and report)
deleted from the database programmatically. Since I've performed dynamic
report generation and maintenance functions before, I don't expect to have
too much trouble with those. In fact, I can currently generate the data I
need using a crosstab query, but as stated before, it's a dumb query that is
only good for display purposes. The easiest fix that I can see would be to
find a way to use the crosstab; I've tried everything I can think of except
exporting it to excel & re-importing as a table.
Hope this helps.
"Jeff Boyce" wrote:

David

It sounds like you are trying to dynamically create new fields named after
partnumbers. If so, that approach embeds data (partnumber) in field names,
a no-no in a relational database.

You've asked a "how" question. It all starts with the data ... please
describe the data you're working with.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"DavidB" wrote in message
...
I'm writing a Access 2007 db to help with inventory control that contains
a
crosstab table which pulls together a product's descriptive elements to
make
a model part number for the product. The query then lists each part's
serial
number below the part number in a column. I want to then take that part
number and dynamically make a table-field out of it with the serial
numbers
listed below. Since this value can change each time the query is run, the
table has to be dynamic and cleared after each use. I've found that
crosstab
queries don't play well with the make-table option in queries or with
reports
at gen time. I thought that by leaving the query as a select query, then
programmatically creating the table fields, I could get around this
irritation. Any ideas?

David

(see comments in-line below)

"DavidB" wrote in message
...
No doubt that this is a significant (but creative) challenge.
To describe the data. I have Dell computers that my company obtains from a
client and is responsible to resell. I have developed a database system
that
through scanning the service tag & the client's asset tag, enables me to
track the device from the time it enters the door to our warehouse till
the
time I resell it. I'm trying to develop a bill of sale that allows me to
segregate the data by the pc descriptive information (ex: GX240
BLK/[client
service tag]) so that I can list each device sold by the descriptive info
in
a column.

If you have a table that lists equipment, you can join that table to a
SoldItem table to get a query that displays "pc descriptive info".

It seems to me that if I can list each sold item (which can be from
one model to ten different models, based on what is available to sell)

I'm too literal! When I read the previous statement, I try to imagine ONE
sold item that is (simultaneously) "ten different models". Can I assume you
mean a [sold item] is one of the models in a table that lists equipment?

under
the descriptive info about that item, it would be most efficient. This
information will also be stored in an "Items Sold" table that is indexed
by
the type of purchaser (reseller, commercial, or employee).

"Indexed" has a different meaning in Access. Do you mean "sorted by"?

The data will be
used to generate a report (dynamically) and then the table (and report)
deleted from the database programmatically.

Whoa! If you are keeping informationa about what you sell, why would you
delete the table? Are you saying that you are creating a temporary table
solely for the purpose of generating a report? If so, be aware that any
queries you use to create that (temporary) table could be used instead as
the source of data for the report ... no deleting required!

Since I've performed dynamic
report generation and maintenance functions before, I don't expect to have
too much trouble with those. In fact, I can currently generate the data I
need using a crosstab query, but as stated before, it's a dumb query that
is
only good for display purposes.

There's no reason you couldn't use that crosstab query as the source for a
report.

The easiest fix that I can see would be to
find a way to use the crosstab; I've tried everything I can think of
except
exporting it to excel & re-importing as a table.
Hope this helps.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP