RE: Add a new column AS date using a prompt or form

I’m not sure how to post a table structure.
Table is linked to a delimited text flat file.
UPC = T12
52_week_doll = D2

I have a monthly report with a rolling 52wk sales by UPC. I would like to
keep track of the 52wk sales dollars in a field with the corresponding period
end date AS the column header (08/11/07, etc.).

Using Either a Make Table or an Append, add a new field for the current
rolling 52wk sales by UPC AS [new period end date].

UPC 07/14/07 08/11/07
000834663049 $19,812.20 $19,812.20
001115211655 $5,323.91 $5,323.91
001117101118 $1,810.80 $1,810.80

I’ve tried to prompt for a date [Enter period end date]. Also tied to enter
date with a form, with no luck.

Working SQL:
SELECT [ACN_TEXT].UPC, [ACN_TEXT]![52_week_doll] AS [08/11/07]
FROM [ACN_TEXT];

--
Thanks, Kevin


"KARL DEWEY" wrote:

A couple of problems I see --
1 - You are calling your table by two names -
FROM [AC NIELSEN TEXT];
and [ACN_TEXT]
2 - [ACN_TEXT]![52_week_doll] AS [Forms![frmDate]![txtStartDate]]
This says you want to use an alias named -- [Forms![frmDate]![txtStartDate]
and you have a double bracket.

Post your table structure - field names and data type. Post sample of your
data.
--
KARL DEWEY
Build a little - Test a little


"AFSSkier" wrote:

I have a monthly sales report. I would like to keep track of the 52 wk sales
dollars in a column field with the period end date AS the column header.
Adding a new column at the end of every period.

UPC 07/14/07 08/11/07
000834663049 $19,812.20 $19,812.20
001115211655 $5,323.91 $5,323.91
001117101118 $1,810.80 $1,810.80

I’ve tried to prompt a date & enter with a form (see SQL), with no luck.

SQL:
SELECT [ACN_TEXT].UPC, [ACN_TEXT]![52_week_doll] AS
[Forms![frmDate]![txtStartDate]]
FROM [AC NIELSEN TEXT];

--
Thanks, Kevin
.

Relevant Pages

  • Re: Possible to Get prior records?
    ... What if you added an extra field in the table you describe, ... number for each period (without gaps)? ... > I have to do a rolling 12-month average of sales. ...
    (microsoft.public.access.queries)
  • Possible to Get prior records?
    ... I have to do a rolling 12-month average of sales. ... problem is that our Month Ending Dates are not standard. ... (for each of our 15 stors), ...
    (microsoft.public.access.queries)
  • Re: bush
    ... MGA/Mitsubishi tv set I bought there a few weeks ago had slight shadow ... lines rolling on the screen,the sales lady charged me $5.00 for the tv ...
    (rec.radio.shortwave)
  • Column Header with Variable
    ... Is it possible to specify the column header with variable? ... If I want to make headerText as Sales and Credit how do I do so? ...
    (microsoft.public.dotnet.framework.aspnet.datagridcontrol)