How / When to Create a Table from a Query

[Nurse Nancy]

I have been reading posts for 24 hours straight and have learned more about
access thru this than I could ever imagine,, so now it's my turn to try it
out. Fingers Crossed!!!!! Here Goes.............

New User, Using access 2007.

The company is a broker for Radio Air Space.

Need to find Radio Stations to run Advertisements for Customers.

Have a table of Radio Stations with Mulitivalue Lookups for Station Formats
(Rock, News, Sports, Country, etc)
and Multiple Values for Target Audiences (Teens, Adults, Athletes, High
Income, Gay/Lesbian, etc).

Have a table of Products/Campaigns with Multiple Values for Target Audiences.

Running a query to find all Radio Stations that match any of the Target
Audiences of a product/campaign to produce a list of Potential Radio Stations
where the Customer should advertise their product.

Once i find the list of matches, this becomes the perspective Buy Table for
the Customer for the upcoming week.

So my thought was that I would want to save the query as a Table and then
allow the Users to tweak it by adding additional Radio Stations that were not
returned in the query or deleting Stations that did not want to sell slots.

Once the table is generated and finalized for the week, it will be used to
invoice, track ROI, track Call Center Info, etc. It may then be used as the
template for upcoming weeks.

So,, how do I save it as table and then allow it to be linked to the Radio
Station Table for add ons, and allow deletions that would not update the
Master Radio Station Table.

Am I on the right track or am i way off?

Also, how do i create a table from a query,, the Save As,, only allows to
Save As a Form or Query

 

[John W. Vinson]

I have been reading posts for 24 hours straight and have learned more about
access thru this than I could ever imagine,, so now it's my turn to try it
out. Fingers Crossed!!!!! Here Goes.............

New User, Using access 2007.

The company is a broker for Radio Air Space.

Need to find Radio Stations to run Advertisements for Customers.

Have a table of Radio Stations with Mulitivalue Lookups for Station Formats
(Rock, News, Sports, Country, etc)
and Multiple Values for Target Audiences (Teens, Adults, Athletes, High
Income, Gay/Lesbian, etc).

Have a table of Products/Campaigns with Multiple Values for Target Audiences.

Lookup fields are were a Bad Idea.
Multivalue lookup fields were a DREADFUL Idea.
Microsoft should be ashamed of themselves for including them in Access.

Running a query to find all Radio Stations that match any of the Target
Audiences of a product/campaign to produce a list of Potential Radio Stations
where the Customer should advertise their product.

Once i find the list of matches, this becomes the perspective Buy Table for
the Customer for the upcoming week.

So my thought was that I would want to save the query as a Table and then
allow the Users to tweak it by adding additional Radio Stations that were not
returned in the query or deleting Stations that did not want to sell slots.

It's neither necessary nor a good idea to create a redundant table for this
purpose. see below.

Once the table is generated and finalized for the week, it will be used to
invoice, track ROI, track Call Center Info, etc. It may then be used as the
template for upcoming weeks.

So,, how do I save it as table and then allow it to be linked to the Radio
Station Table for add ons, and allow deletions that would not update the
Master Radio Station Table.

You don't.

Am I on the right track or am i way off?

Also, how do i create a table from a query,, the Save As,, only allows to
Save As a Form or Query

Well, you COULD turn a query into a MakeTable query or an Append query... but
there's a better way.

You have a many to many relationship from Stations to Formats. The proper
structure for this involves three tables:

Stations
StationID (primary key; could be the call letters)
<information about the station as an entity, e.g. location, power, ...)

Formats
Format (primary key, e.g. "News/Talk", "Country", ...)

StationFormat
StationID
Format

So KBSU-FM Boise would have records in StationFormats like

KBSU-FM Classical
KBSU-FM New Age
KBSU-FM Jazz
KBSU-FM Blues

and perhaps others. (Well, they wouldn't be in your database in the first
place since they are public radio and don't buy ads). <g>

You'll have a similar structure for Target Audiences, and for Products - each
would have an additional "many to many resolver table".

Your work plan could then be based on a query joining the ProductTarget table
to the StationFormat table, to find all the stations that broadcast "Blues"
and link them to all the products that are related to "Blues".

 

[Nurse Nancy]

Hi,
Thanks for your feedback
I don’t know what to do now,, it may be too late for me to change the design
of the db and not use the lookups

Can you explain why you wrote
“Lookup fields are were a Bad Ideaâ€
“ Multivalue lookup fields were a DREADFUL Ideaâ€


I thought I was doing the correct thing,
I have lots of lookup fields in my tables/forms
For instance in the Radio Station Table when a user adds a new radio
station, I force them to enter the following:
Target Audience, Time Slots available, Station Format, Market/City/Region, etc
Each one of these fields is a lookup field pointing to the appropriate table.

How would I enforce valid data if I did not use a Lookup field, especially
since New Fields can be added to those tables?


Next question,
Since the multivalued lookup fields are mainly going to be used for
searching for radio stations, how bad an idea is it to keep them?

What is the harm?


Couple more questions,, hang in there

Your solution of using the many-to-many resolver table, you wrote…..

“Your work plan could then be based on a query joining the ProductTarget table
to the StationFormat table, to find all the stations that broadcast "Blues"
and link them to all the products that are related to "Blues".â€

Wouldn’t this solution return duplicates, ie the Radio Station would show up
many times in the query results? How would I seamlessly get rid of the
duplicates?
I only want the radio station to be listed one time

The other thing I wanted to ask is,, I let Access create the primary key for
all of tables using the autonumber, is that ok,, or do I need to redesign and
set my own primary keys like you advised.

 

[John W. Vinson]

Hi,
Thanks for your feedback
I don’t know what to do now,, it may be too late for me to change the design
of the db and not use the lookups

It's easy to change a lookup (in a table) to a textbox, and keep using the
lookup functionality using Combo Boxes on forms.

Can you explain why you wrote
“Lookup fields are were a Bad Idea”
“ Multivalue lookup fields were a DREADFUL Idea”

http://www.mvps.org/access/lookupfields.htm has a critique. The main problem
from my point of view is that both features encourage the use of Table
Datasheets to interact with data. Table datasheets are *very limited*. The
lookup field helps you get from 0 to 15mph... but makes it HARDER to "shift
into second gear" and further, and get up to highway speed!

I thought I was doing the correct thing,
I have lots of lookup fields in my tables/forms

In forms? Absolutely. Combo boxes (lookups) are essential on forms. But users
shouldn't even SEE a table datasheet, much less be required to use them for
data entry.

For instance in the Radio Station Table when a user adds a new radio
station, I force them to enter the following:
Target Audience, Time Slots available, Station Format, Market/City/Region, etc
Each one of these fields is a lookup field pointing to the appropriate table.

How would I enforce valid data if I did not use a Lookup field, especially
since New Fields can be added to those tables?

You would use a Form with combo boxes, and you would enforce referential
integrity between the tables in the Relationships window.

Adding *new fields* - changing the structure of tables - should be done quite
rarely, however; this would usually happen only if there were new requirements
for the database that were not included in the original design, or if a
necessary field was overlooked in the original implementation. It should NOT
be a routine action in other words!

Next question,
Since the multivalued lookup fields are mainly going to be used for
searching for radio stations, how bad an idea is it to keep them?

They are not necessary for searching for radio stations. The many to many
relationship junction table I suggested lets you do *exactly the same thing*
as the multivalued field does; in fact, Microsoft actually implemented the
multivalue field by creating a many to many relationship junction table,
behind the scenes and concealed from your view.

What is the harm?

Unnecessary limitation on what you can do with it, and confusion on your part
as to how your database is actually working.

Couple more questions,, hang in there

Your solution of using the many-to-many resolver table, you wrote…..

“Your work plan could then be based on a query joining the ProductTarget table
to the StationFormat table, to find all the stations that broadcast "Blues"
and link them to all the products that are related to "Blues".”

Wouldn’t this solution return duplicates, ie the Radio Station would show up
many times in the query results? How would I seamlessly get rid of the
duplicates?
I only want the radio station to be listed one time

No, it would not. There will only be one record in the junction table with
"KBSU/FM" and "Blues" in those respective fields. That is the only record that
a correctly designed query would retrieve.

The other thing I wanted to ask is,, I let Access create the primary key for
all of tables using the autonumber, is that ok,, or do I need to redesign and
set my own primary keys like you advised.

That's a somewhat contentious issue; neither approach is either absolutely
right nor absolutely wrong. An autonumber will automatically be unique, stable
and short (three desiderata of a primary key); but a station's call letters
are also unique (thanks to FCC licensing), stable and short. I'd go with the
autonumbers since you're already doing so. You may want to add a unique Index
on the call letters, and on the name of the genre, so you don't inadvertantly
end up with duplicate records in your table.

 

[Nurse Nancy]

I am so sorry, but i am still not gettin it,

Lets say Hunts ketchup is the product

Target audiences for the product are
Teens
Adults
Kids
Low income
Med Income
Males 18-36
Females
Rural
Etc


Then I have a table that maps target audiences to formats
Target Format

Teens
Rock
Country-Rock
Hip Hop
Etc

Adults
Adult Contempory
Jazz
Easy Listening
Etc

Kids
KIDS
Classic Kids
Hip Hop
ETc

Males 18-36
Sports
Rock
Jazz
ETC


Then I have a Radio Station Table that has 1 or 2 formats for each Station

KIZZ-AM
Rock
Hip Hop

KIBB-FM
Rock
Hip Hop

WEBD-AM
Jazz

WEkk-FM
Jazz

WETT-FM
Sports

WQRR-FM
Hip Hop

WTYY-AM
Hip Hop

Then when I search for all Radio Stations by that have any of the target
audiences/formats for that product,, I’ll get

KIZZ-AM 4x, for Teens-Rock, Males-Rock, Teens-Hip Hop, Males-Hip Hop
as well as other stations multiple times

 

[John W. Vinson]

Then when I search for all Radio Stations by that have any of the target
audiences/formats for that product,, I’ll get

KIZZ-AM 4x, for Teens-Rock, Males-Rock, Teens-Hip Hop, Males-Hip Hop
as well as other stations multiple times

You may want to use a Unique Values query. Perhaps you could open your query
in SQL view and post the SQL text here - I don't know enough about your table
structure and fieldnames to post a query but I'd be glad to tweak one.

 

[Nurse Nancy]

ooh, tell me more about the unique values query, that sounds perfect,,, and i
will send you the sql, as soon as i figure out how to do that!!!

Or can I run some code after the fact to get destinct radio stations?

thanks so much.

You are gonna hate me,, but i really do need to make a table out of the
query when i finally get it working correctly. At least i think i do.

Each Sales Rep will run that query for the advertisers/products they
handle,, the results become 'the suggested' list of radio stations that best
fit the needs of the advertiser.

So each rep will run the query for each of their advertisers/products. They
will then contact the radio stations on the 'suggested list' to see if they
want to sell time slots based on the advertisers budgets.

THe reps often know of other stations that did not get returned in the query
results that they would want to add to their 'suggested list' They also may
permanently delete a station that did not want to sell, or keep it for
future weeks.

That query most likely will never run again, instead the reps will need that
query to be saved as table.

Then I will use that table in additional queries and append additonal
stations and call center info to it.

Bottom line is,, I would like to use a macro or vb to save the query as a
table in the database and use the advertisername_productname_date or prompt
the rep for the name of the table.

Is this possible?

 

[Nurse Nancy]

I found another post regarding the unique values setting on the property
sheet and as long as I do not display the formats or the target audiences in
my query results, it works!!! Your a genius,, so now I just need the make
table query macro!!

 

[John W. Vinson]

I found another post regarding the unique values setting on the property
sheet and as long as I do not display the formats or the target audiences in
my query results, it works!!! Your a genius,, so now I just need the make
table query macro!!

You don't need a macro.

Just change the query to a Make Table query using the Query menu option.

I generally dislike make table queries if the data exists in other tables, but
in this case it may be appropriate.

 

[Nurse Nancy]

Hi,
I think I need a macro, because right now i have a control on a form that
runs the query, and then once the query is finished i need it to
automatically save as a table and named after the advertiser_Product_date or
prompt the user for the table name. Is this doable?

 

[John W. Vinson]

Hi,
I think I need a macro, because right now i have a control on a form that
runs the query, and then once the query is finished i need it to
automatically save as a table and named after the advertiser_Product_date or
prompt the user for the table name. Is this doable?

You'll probably need VBA if you're going to change the name of the table. I
REALLY REALLY dislike the idea of storing information like this in a table
name though!!! Why can't the user just use a dynamic query?

 

[Nurse Nancy]

Can you tell me how.......

Each Sales Rep will run that query for the advertisers/products they
handle,, the results become 'the suggested' list of radio stations that best
fit the needs of the advertiser for the upcoming weeks.

So each rep will run the query for each of their advertisers/products. They
will then contact the radio stations on the 'suggested list' to see if the
stations
want to sell time slots based on the advertisers budgets.

THe reps often know of other stations that did not get returned in the query
results that they would want to add to their 'suggested list' They also may
need to delete a station from the query list that did not want to sell.

After they finalize the 'list' of stations, they will use it going forward
and add addtional stations and update with call center info, ROI, Pricing,
etc.

If i leave it as a query won't it always return the same results every time
something links to it? If the user wants to remove a station for the query
list, how would i do that without deleting the radio station from the station
table linked to the query?

If they want to add station that was not returned in the query, how would i
allow them to add a station without adding a duplicate to the radio station
table?

And lastly, I want to run the query again with different search criteria in
order to filter out stations, or return additional stations, how would i
append the results to the original queried list?

So if you can help me accomplish this without saving as a table, I would be
very greatful,

or if you agree that i need to create tables, please tell me how becuase
each product will need a different table name.

I thought with previous versions of Acces,, you could easily save a query as
a table within the same db.