Update Query?

[kilroyscarnival]

I'm hoping to replace a manual text-add process in Excel to an update query in Access.

I have one table of data (tracking ratings of TV programmes) with a date field.

I have another table of data with the start dates and stop dates of TV ratings "sweep" periods (U.S.).

In the past, I would manually add the "FEB02" tag to any programmes which ran in the four week sweep (it doesn't simply fall within the calendar month--it might be 30 Jan to 26 Feb) before importing to my database.

But there must be a way to do this with an update query or something. I've already made a simple access query which filters those programs running between the dates. Any ideas?

Thank you!

Best regards,

Ann

 

[PookaTech]

If I understand you, all you want to do is tag the string "Feb02" to the end of the text field for the selected records. So if you have the records selected in a query then you can just use an update query to update the program name. A field in an update query with the Field [Program Name] from your select query and the update to property set to ([Program Name] & "Feb02") without the brackets should do the trick.

This is a pretty simple concatenation (is that a word?), but it sounds like what you need.

Good luck,
Pookatech

 

[kilroyscarnival]

<<If I understand you, all you want to do is tag the string "Feb02" to the end of the text field for the selected records. So if you have the records selected in a query then you can just use an update query to update the program name. >>

No, not quite. I want to add the field "Feb02" to the record where it pertains, and "May02", etc. as well. I know this sounds simple, but whatever I did it didn't work.

 

[raskew]

Please explain your rules for determining which 'sweep' to specify for a specific date.

 

[kilroyscarnival]

Please explain your rules for determining which 'sweep' to specify for a specific date.

Sure. In a separate table, I have three columns of data -- the label of the 'sweep' (in text, rather than data form), the start date, and the end date.

In a simple access query, I paired the two tables based upon the criteria:

WHERE Prime.Date >= Sweep Dates.StartDate AND Prime.Date <= Sweep Dates.EndDate

This gets me great results if I just wanted the filtered data which ran in a ratings sweep, but I would really prefer to have all data displayed.

I could take the results of the Access Query, add them to the entire prime table, then eliminate the duplicates, but there's got to be an easier way, I figure. Thanks!

 

[kilroyscarnival]

Okay, here's what I ended up doing... I ran an append query to make a separate file for the data with the "Sweep Date" criteria, adding the appropriate label in the Sweep field. Then I ran a delete query on the original table for data between the sweep dates. Finally I appended the new table back to the old and I had everything in one file. I still think I missed the easier way.

Incidentally, I made the sweep marker text ('FEB02') rather than a date format simply because I could then sort alphabetically and get all the Feb data together across years if I needed it. If I needed it chronologically I could sort by the date field anyway.

Best regards,

Ann