Comparing Months

[Guest]

I posted this on another Access Discussion yesterday, but the reply was
obviously beyond my Access level of intelligence. Thought maybe someone in
this discussion could help me.

have a table that has three three fields with data. An example is below:

Operator Scrap Amount Year-Month
---------- ----------------- --------------
BRAD D $791.00 200612
TIM H $500.00 200611
BRAD D $350.00 200611

I would like to build a query that would take the December amount $791.00
for BRAD D and compare it to his previous month of $350.00 for November. Is
there a way I can do this?

 

[George Nicholson]

- Start a new query in design view
- Add your data table/query
- Change the Query type to Crosstab (on Query Menu)
- Add [Operator] as RowHeader
- Add [Year-Month] as Column Header
- Add [Scrap Amount] as Value and set Total to Sum
- Run query

Should give you results like:
Operator 200611 200612
BRAD D $350.00 $791.00
TIM H $500.00


HTH,

 

[Guest]

The problem I see is the way I think you are storing the date. Are you
using a DateTime datatype field.
If so, then do this.
SELECT SteveY.Operator, [SteveY_1].[Scrap Amount]-[SteveY].[Scrap Amount] AS
[Monthly Compare], SteveY.[Year-Month] AS [Prior Month], SteveY.[Scrap
Amount] AS [Prior Month Scrap], SteveY_1.[Year-Month] AS [Latest Month],
SteveY_1.[Scrap Amount] AS [Latest Month Scrap]
FROM SteveY INNER JOIN SteveY AS SteveY_1 ON SteveY.Operator =
SteveY_1.Operator
GROUP BY SteveY.Operator, [SteveY_1].[Scrap Amount]-[SteveY].[Scrap Amount],
SteveY.[Year-Month], SteveY.[Scrap Amount], SteveY_1.[Year-Month],
SteveY_1.[Scrap Amount], Format([SteveY_1].[Year-Month],"yyyymm")
HAVING
(((Format([SteveY_1].[Year-Month],"yyyymm"))=Format(DateAdd("m",1,[SteveY].[Year-Month]),"yyyymm")))
ORDER BY SteveY.Operator, SteveY.[Year-Month];

 

[Guest]

George:
I think is getting me where I want to be thank you. But I do have one other
question. Right now I am importing my data from an Excel spreadsheet. When
I import it, each record has an actual date. Is there any method to convert
a date so that it is split up into two seperate columns on a table? For
example. A record would come across right now with a date of 02/23/2007. It
would be helpful in my building and comparing of information if I were to
convert this date to FEBRUARY and 2007 in two seperate columns on a table.
Can you help?

- Start a new query in design view
- Add your data table/query
- Change the Query type to Crosstab (on Query Menu)
- Add [Operator] as RowHeader
- Add [Year-Month] as Column Header
- Add [Scrap Amount] as Value and set Total to Sum
- Run query

Should give you results like:
Operator 200611 200612
BRAD D $350.00 $791.00
TIM H $500.00


HTH,

I posted this on another Access Discussion yesterday, but the reply was
obviously beyond my Access level of intelligence. Thought maybe someone
in
this discussion could help me.

have a table that has three three fields with data. An example is below:

Operator Scrap Amount Year-Month
---------- ----------------- --------------
BRAD D $791.00 200612
TIM H $500.00 200611
BRAD D $350.00 200611

I would like to build a query that would take the December amount $791.00
for BRAD D and compare it to his previous month of $350.00 for November.
Is
there a way I can do this?

 

[gls858]

You can leave it date form and use the DatePart function to pull the
year or month. Check the help files for the DatePart function.

gls858

George:
I think is getting me where I want to be thank you. But I do have one other
question. Right now I am importing my data from an Excel spreadsheet. When
I import it, each record has an actual date. Is there any method to convert
a date so that it is split up into two seperate columns on a table? For
example. A record would come across right now with a date of 02/23/2007. It
would be helpful in my building and comparing of information if I were to
convert this date to FEBRUARY and 2007 in two seperate columns on a table.
Can you help?

- Start a new query in design view
- Add your data table/query
- Change the Query type to Crosstab (on Query Menu)
- Add [Operator] as RowHeader
- Add [Year-Month] as Column Header
- Add [Scrap Amount] as Value and set Total to Sum
- Run query

Should give you results like:
Operator 200611 200612
BRAD D $350.00 $791.00
TIM H $500.00


HTH,

I posted this on another Access Discussion yesterday, but the reply was
obviously beyond my Access level of intelligence. Thought maybe someone
in
this discussion could help me.

have a table that has three three fields with data. An example is below:

Operator Scrap Amount Year-Month
---------- ----------------- --------------
BRAD D $791.00 200612
TIM H $500.00 200611
BRAD D $350.00 200611

I would like to build a query that would take the December amount $791.00
for BRAD D and compare it to his previous month of $350.00 for November.
Is
there a way I can do this?

 

[Guest]

Look up Formatting.
--
KARL DEWEY
Build a little - Test a little

George:
I think is getting me where I want to be thank you. But I do have one other
question. Right now I am importing my data from an Excel spreadsheet. When
I import it, each record has an actual date. Is there any method to convert
a date so that it is split up into two seperate columns on a table? For
example. A record would come across right now with a date of 02/23/2007. It
would be helpful in my building and comparing of information if I were to
convert this date to FEBRUARY and 2007 in two seperate columns on a table.
Can you help?

- Start a new query in design view
- Add your data table/query
- Change the Query type to Crosstab (on Query Menu)
- Add [Operator] as RowHeader
- Add [Year-Month] as Column Header
- Add [Scrap Amount] as Value and set Total to Sum
- Run query

Should give you results like:
Operator 200611 200612
BRAD D $350.00 $791.00
TIM H $500.00


HTH,

I posted this on another Access Discussion yesterday, but the reply was
obviously beyond my Access level of intelligence. Thought maybe someone
in
this discussion could help me.

have a table that has three three fields with data. An example is below:

Operator Scrap Amount Year-Month
---------- ----------------- --------------
BRAD D $791.00 200612
TIM H $500.00 200611
BRAD D $350.00 200611

I would like to build a query that would take the December amount $791.00
for BRAD D and compare it to his previous month of $350.00 for November.
Is
there a way I can do this?

 

[John W. Vinson]

George:
I think is getting me where I want to be thank you. But I do have one other
question. Right now I am importing my data from an Excel spreadsheet. When
I import it, each record has an actual date. Is there any method to convert
a date so that it is split up into two seperate columns on a table? For
example. A record would come across right now with a date of 02/23/2007. It
would be helpful in my building and comparing of information if I were to
convert this date to FEBRUARY and 2007 in two seperate columns on a table.
Can you help?

Well... you really DON'T want to do this.

Doing so might make sense in a spreadsheet, but Access isn't Excel, and a
Table isn't a Spreadsheet. One basic principle of table design is that each
field in a table should be dependent on the Primary Key of the table, *and on
nothing else* - that is, you should NOT have a field for the date and also a
field for the month, since the month depends directly on the date.

What you *can* do is to store just the date in your table, and create a Query
based on the table; in this Query you can have two calculated fields:

TheYear: Year([datefield])
TheMonth: Format([datefield], "mmmm")

to get the year as an integer 2007, and the month as a Text string "February".
This Query can then be used in conjunction with other tables, as needed.


If you can describe how you're "comparing information" and what you're
comparing it with, we might be able to suggest a query to do so efficiently.

John W. Vinson [MVP]