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.

 

[pastels]

Suggestions anyone?

Norie - any luck?

Thanks.

 

[Norie]

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.

I'm sorry but I can't really give a more specific answer.

To create code like this would be totally dependent on exactly what tables/fields etc you have.

 

[pastels]

Is it possible to create a generic one? I can test and modify to fit what I'm trying to do...

 

[Norie]

Can you give more information on what tables/fields you have? eg names etc

And also from them what result you want?

I know you gave some information previously but more woulde be useful.

 

[SydneyGeek]

Sounds like this is a case for database replication. Essentially, you maintain a Master database with all the data and distribute replicas to your reps, with the data they need. When they make changes, those changes are rolled into the Master at synchronisation time and the next set of replicas will pick up those chagnes plus any make by the administrator. It's a big topic with at least one gotcha -- there is no going back once you create a replication master, so keep a copy of the original handy in case you change your mind. Too much detail fo rthis post, but check out this link for an overview.

Denis

 

[pastels]

Sorry for the late response, I've been preoccupied lately.

Thanks Denis for your suggestion. That's something I can use for another project but not what I need for this one. Thanks though!

Hi Norie,

The table name that I'd like to use is "tblTransactions", fields are "RepEmail","RepMgr", "State", "TotalSales". I made the list short to make it simple, I can probably adjust for other fields.

As an example I'll use three reps "Wilma@Acme.com", "Fred@Acme.com", "Barney@Acme.com". Wilma has 10 states to her name, Fred has 15 and Barney has 12. Wilma and Fred are managed by BamBam and while Barney is managed by Pebbles (BamBam and Pebbles is in field "RepMgr"

If the request is for a Access mdb, I'll do the following. I'll create a table from the "tblTransactions" for Wilma, Fred and Barney with criteria by their name, ie I'll create a Make Table Query, bringing in "tblTransactions" drag all the fields to the grid. Under criteria for field "RepEmail" I'll put "Wilma@Acme.com", name the table "tblWilma_todaysdate" then run the query. I'll repeat the process for Fred and Barney. Once that's done I'll create a new blank mdb for each of the rep, ie "WilmaTransactions_todaysdate.mdb" and then import the table I've created ("tblWilma_todaysdate") and repeat for all other reps. Then I go to Outlook and create a new mail message for Wilma copying her manager BamBam and attaching the her mdb. If the mdb exceeds 5mb I zip it up and reattach. I repeat the same process for the other reps. (hope I'm making sense here).

If the request is for Excel, it's the same process as above but instead of creating an mdb I export the table in Excel format and that's what I attach (also zip if more that 5mb).

Hope this help you help me...

 

[SydneyGeek]

Not sure what level your coding skills are at, but I have put together a sample database that creates the tables for you, using a form to set the parameters.
Send a PM with your email address and I'll pass it on to you -- Access 2000 version. If you need 97, let me know

Denis

 

[pastels]

Thanks Denis. I've PM the info. Let me know when you've sent it so I can test. I'm a bit excited.

Thanks.