Add a new column AS date using a prompt or form

A

AFSSkier

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];
 
K

KARL DEWEY

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.
 
J

John Spencer

Take a look at crosstab queries. Beyond that I can't advise you as I
know nothing about your table structure.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
S

Steve

It takes a dynamic crosstab query to do this. You won't be able to display
all 52 weeks at the same time - just won't fit on a sheet of paper!! You can
probably display eight to ten periods max. You need a form where you select
the range of period ending dates you want to display in the report and then
click a button to launch the report. If you wanted to report 52 weeks, you
could automate the above process to produce seven reports with eight
successive periods on each report. I guess you could set it up to print one
to seven reports depending upon the range of period ending dates you
selected. None of this is trivial!! I could set it all up for you for a very
reasonable fee.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
A

AFSSkier

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 said:
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 said:
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];
Click to expand...
Click to expand...
 
K

KARL DEWEY

I’m not sure how to post a table structure.
Post the field names and data type like this ---
UPC - Text
Price - Number - Single
EventDate - DateTime
Happy - Yes/No
Etc - xxxx
 
J

John W. Vinson

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.).
Click to expand...

That's a Crosstab Query, and it certainly should NOT be stored in a table.

John W. Vinson [MVP]
 
A

AFSSkier

It is a LinkTable to a delimited text flat file.

UPC - Text
52_week_doll – Number - Double

UPC 52_week_doll (period end 08/11/07)
000834663049 $19,812.20
001115211655 $5,323.91
001117101118 $1,810.80

The monthly report only has two fields [UPC] & [52_week_doll]. It's not in
a list with dates for a CrossTab. To an existing table, I want to add a new
field each month [52_week_doll] AS period end date (08/11/07 for example).

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

KARL DEWEY

What you need is an append query with three fields to append the linked file.
Your table needs thre fields only --
UPC Your_Dollar_Field Your_Import_Month
The import moth to be a datetime field. Your query to look like this ---
INSERT INTO transactions ( UPC, Your_Dollar_Field, Your_Import_Month )
SELECT Table1.YourUPCField, Table1.Your_Dollar_Field, Date()-Day(Date()) AS
Expr1
FROM Table1;
This will append the last day of last month to the file with the linked data.
Then you can use a crosstab query.

--
KARL DEWEY
Build a little - Test a little


AFSSkier said:
It is a LinkTable to a delimited text flat file.

UPC - Text
52_week_doll – Number - Double

UPC 52_week_doll (period end 08/11/07)
000834663049 $19,812.20
001115211655 $5,323.91
001117101118 $1,810.80

The monthly report only has two fields [UPC] & [52_week_doll]. It's not in
a list with dates for a CrossTab. To an existing table, I want to add a new
field each month [52_week_doll] AS period end date (08/11/07 for example).

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

--
Thanks, Kevin


KARL DEWEY said:
Post the field names and data type like this ---
UPC - Text
Price - Number - Single
EventDate - DateTime
Happy - Yes/No
Etc - xxxx
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date problem in Pivot Tables when using SQL as a Data Source. 1

Top