Append Copy of Table

[Nurse Nancy]

Hi
I have a bunch of append queries that find Radio Stations based on different
search criteria for a given Advertiser. and then append records to a weekly
Buy Table.

Once the Sales REp has the weekly Buy Table completed for the first week,
they will then want to replicate that table for 'n' number of weeks depending
on how long an advertising campaing will run.

Currently they do this using excel spreadsheets and they just keep reusing
the same table and changing the date.

Now I have everything loaded into Access2007 and have automated the search
and the creation of the first weeks table. (I don't want to create all the
weeks at this point,, bc they do a lot of tweaking ie, delete and add
stations depending on budgets, slots available etc.)

After they are done tweaking, How can I copy the records and append to the
table with the new week start date for 'n' weeks?

The first time I append it is based on the campaign start date
I have
Week Start Date = Campaign Start Date
Number of Weeks
End Date (Calculated from Campaign Start Date and Number of Weeks)

Basically I would want to provide a button to create the buy table with new
week start date for 'n' number of weeks

can this be done???

 

[Michael J. Strickland]

You probably want to create a blank table (new buy table), run your
append query to append the buy records to this table. Then create the
button and put VBA code behind it (in the button click event) to
replicate the records of that table "n" times.

Alternatively, you could create a form with the button on it. Then
you could have the button click event:

1. Create the new buy table (blank).

2. Import the Excel records or extract records from the master table
which meet your criteria.

3. Copy/Add these records to the new buy table.

You could do away with any queries all together by reading each record
of the the source data (table) and if it meets your criteria, append it
to the buy table. You could also use the recordset Find method to find
the records you want without manually reading each record.

 

[Nurse Nancy]

i am a newbie so don't know VBA, can you help me with that?
Also I am confused a bit.

Ultimately I only want 1 buy table.
I need to replicate the records with new (week start dates) for 'n' number
of weeks.

 

[Gina Whipp]

Nancy,

You could use Make Table queries... look that up in help BUT that is going
to cause database bloating. Why can't you simply use regular queries that
display in a form which can be made to look like tables?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Michael J. Strickland]

To do this without VBA, you would just replicate the records of the
Weekly Buy table (once for each week of the campaign) into a "Campaign"
table. Then you need to edit the dates for the later weeks.

Assume your table you append records to is call Weekly_Buy.

1. Copy the weekly buy table to a new table named Campaign_Buy)
a. Click on the table name.
b. Hit Ctl-C.
c. Hit Ctl-V.
d. Rename the new table (e.g. to Campaign_Buy).
e. Click Ok.

2. To add a 2nd week to the Campaign buy table, copy its records onto
the end of it (the Campaign_Buy table).
a. Open the table
b. Click on the block in the upper left corner (to select all the
records).
c. Hit Ctl-C (Copy).
d. Click on the asterisk to the left side of the bottom row of the
table.
e. Hit Ctl-V (Paste).

To create more weeks, just repeat steps 2d & 2e to paste an
additional week (the records will still be in the paste buffer).

3. Now you have to edit the dates for all but the first set of records.

Using VBA (which I can't teach you over Usenet), you could automate this
and do the date editing as you paste the records.

It might be easier to create a separate Buy table for each week of the
campaign by copying the original Weekly table to n different tables.
Again, you would have to edit the dates in these tables to reflect the
correct dates.

 

[Nurse Nancy]

Hi
Please tell me if there is a better way.


I created a weekly Buy Table from append queries. Now I need to replicate
the records except update the week start date for 'n' number of weeks, where
'n' = the number of weeks a campaign will run.



Hi

I have a bunch of append queries that find Radio Stations based on
different
search criteria for a given Advertiser. and then append records to a
weekly
Buy Table.

Once the Sales REp has the weekly Buy Table completed for the first week,
they will then want to replicate that table for 'n' number of weeks
depending
on how long an advertising campaing will run.

 

[Gina Whipp]

You are asking for us to tell you if there is a better way but we're going
to need a wee bit more information. Like why do you need to append to a
seperate table? How are your tables set-up? What are you storing in the
tables?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Nurse Nancy]

Ok, here goes

This db has a table of Radio Stations and a Campaign Table
The Campaign Talble holds the advertiser/customer info regarding the product
they want to advertise:
PK - Campaign ID
Customer ID
Target Audience - multi value select
Budget
Number of Weeks the campaign will run,ie number of weeks they want to buy
airtime from stations
Start Date
End Date
Sales Rep
Market/City they want to advertise - multi value select
RAnking - w/in Top 10, w/in Top 100, etc


i have a query that uses the information that is selected on the Campaign
Form that goes out and finds radio stations that match the criteria.
Ie Radio stations with Formats that match the target audience in the desired
cities, etc

So I may get 50 stations that match the criteria,
I then append this to Buy Table

Each air-time contract for the given campaign ID would be stored in Buy Table
PK buy_id,
FK campaign_id,
FK radio_station_id,
buy_status
comments.
Keep/Delete - Yes No Checkbox

The sales rep then reaches out to the selected stations based on the
advertisers budget to see if they have any remnant time slots for sale.

Based on the responses,, stations are deleted from the buy table or new ones
are added in
Once the Sales Rep has confirmed buys from the final list of stations,, then
this info is used for billing and invoices weekly.

The problem is,, that the campaign may run for 4 or 5 weeks, so i thought i
needed to duplicat the radio station buys with a new week start date for 'n'
number of weeks in order to bill and invoce weekly.

 

[Gina Whipp]

Nancy,

First of all, thank you for giving us the FULL picture, it helps... In your
scenario, so no recreation is needed I would append the records to tblBuy
and add a field ClosedCampaign. That way I am not recreating the table AND
I still have historical data should I need it. Now if it's the same
stations from week to week then why duplicate the records? You would only
need to check a box if the Station wants to drop a week. I imagine you are
using a report to print off for billing purposes?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Nurse Nancy]

Hey Gina,
I am still stuck on this.
I still think i need to copy the records to the buy table for the next week
if they are confirmed for the current week. I like your idea about the
check box, and so do the users, but I don't know how to implement it. When I
mentioned your idea about the closed campaign and the drop a week check box,,
they started asking for a checkboxes that would say
confirmed for enitire campaign
confirmed for week1,
confirmed for week2, etc


Again i have no idea how to implement this because the Buy rates and posting
rates are billed on weekly basis and won't i lose this info If I recalc it
and don't store it?