Crosstab Help

  • Thread starter Thread starter Jason Sweet
  • Start date Start date
J

Jason Sweet

Thanks in advance for looking at this!

I have a simple crosstab that looks like:

SKU 02/02/04 11/15/03
0060086157 0.00 2.00
0060505117 2.00 3.00
0060510323 0.00 2.00
0060591838 1.00 3.00

I would like to add (if possible) a simple function that subtracts the value
of "02/02/04" from the value of "11/15/03".

So...

SKU 02/02/04 11/15/03 Difference
0060086157 0.00 2.00 2.00
0060505117 2.00 3.00 1

Any assistance would be greatly appreciated. For reference, I have included
the SQL statement:

TRANSFORM Sum(report.count) AS SumOfcount
SELECT report.SKU, Sum(report.count) AS [Total Of count]
FROM report
GROUP BY report.SKU
PIVOT report.date;

Thanks! Jason Sweet
 
You could use
Diff:Sum(Abs([Date]=#2/2/04#) * [Count]) - Sum(Abs([Date]=#11/15/03#) *
[Count])
Since your date columns are probably dynamic, this won't work much of the
time.
You might be able to subtract the Max from the Min or something but you
haven't given enough information to tell for sure.
 
Duane,

Thanks for the response. Yes, the dates will be dynamic
so I can't reference specific dates. What other
information might I provide that would be helpful?

- Jason Sweet
-----Original Message-----
You could use
Diff:Sum(Abs([Date]=#2/2/04#) * [Count]) - Sum(Abs([Date] =#11/15/03#) *
[Count])
Since your date columns are probably dynamic, this won't work much of the
time.
You might be able to subtract the Max from the Min or something but you
haven't given enough information to tell for sure.

--
Duane Hookom
MS Access MVP


Thanks in advance for looking at this!

I have a simple crosstab that looks like:

SKU 02/02/04 11/15/03
0060086157 0.00 2.00
0060505117 2.00 3.00
0060510323 0.00 2.00
0060591838 1.00 3.00

I would like to add (if possible) a simple function
Click to expand...
that subtracts the
value
of "02/02/04" from the value of "11/15/03".

So...

SKU 02/02/04 11/15/03 Difference
0060086157 0.00 2.00 2.00
0060505117 2.00 3.00 1

Any assistance would be greatly appreciated. For
Click to expand...
reference, I have
included
the SQL statement:

TRANSFORM Sum(report.count) AS SumOfcount
SELECT report.SKU, Sum(report.count) AS [Total Of count]
FROM report
GROUP BY report.SKU
PIVOT report.date;

Thanks! Jason Sweet
Click to expand...


.
Click to expand...
 
In a queries NG, it is usually good practice to post the SQL view of your
query.

--
Duane Hookom
MS Access MVP


Jason Sweet said:
Duane,

Thanks for the response. Yes, the dates will be dynamic
so I can't reference specific dates. What other
information might I provide that would be helpful?

- Jason Sweet
-----Original Message-----
You could use
Diff:Sum(Abs([Date]=#2/2/04#) * [Count]) - Sum(Abs([Date] =#11/15/03#) *
[Count])
Since your date columns are probably dynamic, this won't work much of the
time.
You might be able to subtract the Max from the Min or something but you
haven't given enough information to tell for sure.

--
Duane Hookom
MS Access MVP


Thanks in advance for looking at this!

I have a simple crosstab that looks like:

SKU 02/02/04 11/15/03
0060086157 0.00 2.00
0060505117 2.00 3.00
0060510323 0.00 2.00
0060591838 1.00 3.00

I would like to add (if possible) a simple function
Click to expand...
that subtracts the
value
of "02/02/04" from the value of "11/15/03".

So...

SKU 02/02/04 11/15/03 Difference
0060086157 0.00 2.00 2.00
0060505117 2.00 3.00 1

Any assistance would be greatly appreciated. For
Click to expand...
reference, I have
included
the SQL statement:

TRANSFORM Sum(report.count) AS SumOfcount
SELECT report.SKU, Sum(report.count) AS [Total Of count]
FROM report
GROUP BY report.SKU
PIVOT report.date;

Thanks! Jason Sweet
Click to expand...


.
Click to expand...
Click to expand...
 
I'm pretty sure SQL statement for the crosstab is at the
bottom of my original message...:)
-----Original Message-----
In a queries NG, it is usually good practice to post the SQL view of your
query.

--
Duane Hookom
MS Access MVP


Duane,

Thanks for the response. Yes, the dates will be dynamic
so I can't reference specific dates. What other
information might I provide that would be helpful?

- Jason Sweet
-----Original Message-----
You could use
Diff:Sum(Abs([Date]=#2/2/04#) * [Count]) - Sum(Abs
Click to expand...
([Date]
=#11/15/03#) *
[Count])
Since your date columns are probably dynamic, this
Click to expand...
won't
work much of the
time.
You might be able to subtract the Max from the Min or something but you
haven't given enough information to tell for sure.

--
Duane Hookom
MS Access MVP


[email protected]...
Thanks in advance for looking at this!

I have a simple crosstab that looks like:

SKU 02/02/04 11/15/03
0060086157 0.00 2.00
0060505117 2.00 3.00
0060510323 0.00 2.00
0060591838 1.00 3.00

I would like to add (if possible) a simple function that subtracts the
value
of "02/02/04" from the value of "11/15/03".

So...

SKU 02/02/04 11/15/03 Difference
0060086157 0.00 2.00 2.00
0060505117 2.00 3.00 1

Any assistance would be greatly appreciated. For reference, I have
included
the SQL statement:

TRANSFORM Sum(report.count) AS SumOfcount
SELECT report.SKU, Sum(report.count) AS [Total Of count]
FROM report
GROUP BY report.SKU
PIVOT report.date;

Thanks! Jason Sweet




.
Click to expand...
Click to expand...


.
Click to expand...
 
Sorry about that.
I would create this with a crosstab query. Rather then generating actual
dates as column headings, use "relative" dates. They would be relative to a
date in a textbox on a form.
Form: frmSelectDate
Textbox: txtEndDate

Create a crosstab query with Position as the Row Heading, HoursWorked as the
Value (Sum), and an expression as the Column Heading:
ColHead:"Day" & DateDiff("d",[Date],Forms!frmSelectDate!txtEndDate)
Set the Column Headings property to: "Day0","Day1","Day2","Day3",....
Go into the query parameters and enter:
Forms!frmSelectDate!txtEndDate Date/Time

This will create a fixed number of columns with a work date ending on the
date entered into the form. In the report, you can create column "labels"
with text boxes with control sources of:
=Forms!frmSelectDate!txtEndDate
=Forms!frmSelectDate!txtEndDate-1
=Forms!frmSelectDate!txtEndDate-2
=Forms!frmSelectDate!txtEndDate-3
=Forms!frmSelectDate!txtEndDate-4
You can then subtract Day0 from Day1 or whatever in a report.

--
Duane Hookom
MS Access MVP


Jason Sweet said:
I'm pretty sure SQL statement for the crosstab is at the
bottom of my original message...:)
-----Original Message-----
In a queries NG, it is usually good practice to post the SQL view of your
query.

--
Duane Hookom
MS Access MVP


Duane,

Thanks for the response. Yes, the dates will be dynamic
so I can't reference specific dates. What other
information might I provide that would be helpful?

- Jason Sweet
-----Original Message-----
You could use
Diff:Sum(Abs([Date]=#2/2/04#) * [Count]) - Sum(Abs ([Date]
=#11/15/03#) *
[Count])
Since your date columns are probably dynamic, this won't
work much of the
time.
You might be able to subtract the Max from the Min or
something but you
haven't given enough information to tell for sure.

--
Duane Hookom
MS Access MVP


[email protected]...
Thanks in advance for looking at this!

I have a simple crosstab that looks like:

SKU 02/02/04 11/15/03
0060086157 0.00 2.00
0060505117 2.00 3.00
0060510323 0.00 2.00
0060591838 1.00 3.00

I would like to add (if possible) a simple function
that subtracts the
value
of "02/02/04" from the value of "11/15/03".

So...

SKU 02/02/04 11/15/03 Difference
0060086157 0.00 2.00 2.00
0060505117 2.00 3.00 1

Any assistance would be greatly appreciated. For
reference, I have
included
the SQL statement:

TRANSFORM Sum(report.count) AS SumOfcount
SELECT report.SKU, Sum(report.count) AS [Total Of
count]
FROM report
GROUP BY report.SKU
PIVOT report.date;

Thanks! Jason Sweet




.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top