Change table name and field field name base on query result

[pastels]

Hello,

Is it possible to change the name of a table and/or field name using the result of a query? For example, tbl1 exist with fields "City","State","Date Created","Total Sales". I'd like to use the max of "State" + todays date as the table name and max of "Date Created" added to the right of field "Total Sales"?

Any possibilities?

Thanks.

 

[Norie]

The only way I can think of doing something like that would be you use VBA code.

What are you actually trying to acheive?

 

[pastels]

Re: Change table name and field field name base on query res

Hi Norie,

I have a table that is updated weekly. It contains thousands sometimes in the million # of records. This table contains sales rep names, order transaction data, address. Basically, every week I manually create separate tables for all records that belongs to a sales rep which I then put to individual Access mdb that I email to the rep (only if it's less than 5mb). If it's greater than 5mb I zip it up. I'm just looking for a more efficient way of doing it.

Any help would be greatly appreciated.

Thanks.

 

[Norie]

Why are you creating seperate tables?

Why not just create one table that summarizes the data and is grouped by rep?

You could then use a parameter to pull each individual rep's information.

If this is something you do on a regular basis I would recommend you automate it using VBA.

 

[pastels]

Re: Change table name and field field name base on query res

Hi Norie,

I do create a summarized table but I need to create a table for each of the rep and put it in a new access database which I separately send to each of the rep.

This is something I process on a regular basis and was hoping to automate the process. The only thing I know about VBA is recording it in Excel. I'm clueless in Access on where to start.

Please help

 

[Norie]

Can you give more information?

Do you have a list of the reps?

Do you really need to create a new database?

Can't you just send the query in Excel or HTML format?

 

[pastels]

Re: Change table name and field field name base on query res

Can you give more information?

Basically, here's what's happening (example...)
- I have an Access database where my Admin updates the sales table with daily sales transaction.
- I then update the table with the rep who is assigned to the location.
- The range of records for each rep is from 10,000 to 80,000 (sometimes more, sometimes less).
- Admin tells me to send report to the reps for all transactions for week of Jan 17-23
- I then create a table for each of the rep, I then put it in a new Access database.


Do you have a list of the reps?

To protect the innocent =) (in reality it's around 80 - 100 reps)
- John Doe
- Mike Brown
- Joe Black

Do you really need to create a new database?

- Yes. I can only create what's requested. Sometimes I create it in Excel (if it fits).
- When the request is Access format, once the rep receives the Access database they create reports out of them.

Can't you just send the query in Excel or HTML format?

- If requested. I haven't tried creating in HTML.

 

[pastels]

Re: Change table name and field field name base on query res

Norie?

If not, thanks anyways.

 

[Norie]

pastels

Sorry for not replying but the answers you gave kind of restrict you.

My idea was to create code that would loop through the sales table and create queries to extract the required data for each individual rep.

You could either retain these queries or export them to Excel.

I've done similar things in the past but I don't know if I have suitable code here at home.

I'll have a look.

 

[pastels]

Re: Change table name and field field name base on query res

Thanks!!, anything that will make this easier I'll take. Until then, I'll be processing the hard way.