Last Years Sales Data based on Fiscal Week

[Guest]

Hello,
I have a DB that keeps Sales Records that are collected weekly(every Sat).
This is a mass amount of data with many records based on the week that they
are collected. I have a table that has the Report Date(the saturday
representing that week)and the sales units.. etc. I can run a querie that
gives me a total sum for that week. I also need the total sales from the same
week last year.
To help with my querie I created a table with Report Date( this is the join)
and added fields such as Year, Fiscal Year and Fiscal Week. The table is
fully loaded with all possible Report dates, along with Year, The Fiscal year
and Fiscal week. I think it would be easier to get last years data by using
these fields in my querie. Any suggestions would be greatly appreciated.

Sincerely,

Chris

 

[Crystal]

Hi Diggsy,

make these calculated fields in your query:

YW: format([datefield],"yy") &
format(DatePart("ww",[datefield],vbSaturday),"00")

for last year:
YprevW: format(DateAdd("y",-1,[datefield]),"yy") &
format(DatePart("ww",[datefield],vbSaturday),"00")

after you put these fields into your query, I think you will
see what to do... if not, post back

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Guest]

Thanks for replying. I just don't understand the the vb Saturday.All dates
happen to be a saturday that's all. Sorry for the confusion. I am showing you
an edited version of my Report Tables and my Sales table.As you can see I
would need to get the sales units for week 1 from feb 05,2005 and feb, 04
2006 and so forth.

Report Date Year Fiscal Year Week
05-Feb-05 2005 2006 1
12-Feb-05 2005 2006 2
19-Feb-05 2005 2006 3
04-Feb-06 2006 2007 1
11-Feb-06 2006 2007 2
18-Feb-06 2006 2007 3

Report Date Sales Units

05-Feb-05 4511
12-Feb-05 3724
19-Feb-05 2452
04-Feb-06 2878
11-Feb-06 3751
18-Feb-06 2982

Thank You

Chris

Hi Diggsy,

make these calculated fields in your query:

YW: format([datefield],"yy") &
format(DatePart("ww",[datefield],vbSaturday),"00")

for last year:
YprevW: format(DateAdd("y",-1,[datefield]),"yy") &
format(DatePart("ww",[datefield],vbSaturday),"00")

after you put these fields into your query, I think you will
see what to do... if not, post back

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

Hello,
I have a DB that keeps Sales Records that are collected weekly(every Sat).
This is a mass amount of data with many records based on the week that they
are collected. I have a table that has the Report Date(the saturday
representing that week)and the sales units.. etc. I can run a querie that
gives me a total sum for that week. I also need the total sales from the same
week last year.
To help with my querie I created a table with Report Date( this is the join)
and added fields such as Year, Fiscal Year and Fiscal Week. The table is
fully loaded with all possible Report dates, along with Year, The Fiscal year
and Fiscal week. I think it would be easier to get last years data by using
these fields in my querie. Any suggestions would be greatly appreciated.

Sincerely,

Chris

 

[strive4peace]

Hi Diggsy,

you can remove the VbSaturday argument -- it is optional and
just means that is the first day of the "week" for counting
-- normally, the first day is Sunday.

Did you try what I suggested?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

Thanks for replying. I just don't understand the the vb Saturday.All dates
happen to be a saturday that's all. Sorry for the confusion. I am showing you
an edited version of my Report Tables and my Sales table.As you can see I
would need to get the sales units for week 1 from feb 05,2005 and feb, 04
2006 and so forth.

Report Date Year Fiscal Year Week
05-Feb-05 2005 2006 1
12-Feb-05 2005 2006 2
19-Feb-05 2005 2006 3
04-Feb-06 2006 2007 1
11-Feb-06 2006 2007 2
18-Feb-06 2006 2007 3

Report Date Sales Units

05-Feb-05 4511
12-Feb-05 3724
19-Feb-05 2452
04-Feb-06 2878
11-Feb-06 3751
18-Feb-06 2982

Thank You

Chris



:

Hi Diggsy,

make these calculated fields in your query:

YW: format([datefield],"yy") &
format(DatePart("ww",[datefield],vbSaturday),"00")

for last year:
YprevW: format(DateAdd("y",-1,[datefield]),"yy") &
format(DatePart("ww",[datefield],vbSaturday),"00")

after you put these fields into your query, I think you will
see what to do... if not, post back

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

Hello,
I have a DB that keeps Sales Records that are collected weekly(every Sat).
This is a mass amount of data with many records based on the week that they
are collected. I have a table that has the Report Date(the saturday
representing that week)and the sales units.. etc. I can run a querie that
gives me a total sum for that week. I also need the total sales from the same
week last year.
To help with my querie I created a table with Report Date( this is the join)
and added fields such as Year, Fiscal Year and Fiscal Week. The table is
fully loaded with all possible Report dates, along with Year, The Fiscal year
and Fiscal week. I think it would be easier to get last years data by using
these fields in my querie. Any suggestions would be greatly appreciated.

Sincerely,

Chris

 

[Guest]

.. So Sorry Do respond so late I was away. I did try it and it kept telling me
I was referencing the Field Report Date from more than one table. i tried
correcting this and it told me I did not have enought arguments. I would like
to say that the Week No is just a number and not a date field. Year and
Fiscal Year are numbers as well. The only date field I have is report date
which is the join between the Report Dates Table and Buyer Sales Table.
Thanks for your responses so far.

sincerely,

Chris

Hi Diggsy,

you can remove the VbSaturday argument -- it is optional and
just means that is the first day of the "week" for counting
-- normally, the first day is Sunday.

Did you try what I suggested?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

Thanks for replying. I just don't understand the the vb Saturday.All dates
happen to be a saturday that's all. Sorry for the confusion. I am showing you
an edited version of my Report Tables and my Sales table.As you can see I
would need to get the sales units for week 1 from feb 05,2005 and feb, 04
2006 and so forth.

Report Date Year Fiscal Year Week
05-Feb-05 2005 2006 1
12-Feb-05 2005 2006 2
19-Feb-05 2005 2006 3
04-Feb-06 2006 2007 1
11-Feb-06 2006 2007 2
18-Feb-06 2006 2007 3

Report Date Sales Units

05-Feb-05 4511
12-Feb-05 3724
19-Feb-05 2452
04-Feb-06 2878
11-Feb-06 3751
18-Feb-06 2982

Thank You

Chris



:

Hi Diggsy,

make these calculated fields in your query:

YW: format([datefield],"yy") &
format(DatePart("ww",[datefield],vbSaturday),"00")

for last year:
YprevW: format(DateAdd("y",-1,[datefield]),"yy") &
format(DatePart("ww",[datefield],vbSaturday),"00")

after you put these fields into your query, I think you will
see what to do... if not, post back

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com



Diggsy wrote:

Hello,
I have a DB that keeps Sales Records that are collected weekly(every Sat).
This is a mass amount of data with many records based on the week that they
are collected. I have a table that has the Report Date(the saturday
representing that week)and the sales units.. etc. I can run a querie that
gives me a total sum for that week. I also need the total sales from the same
week last year.
To help with my querie I created a table with Report Date( this is the join)
and added fields such as Year, Fiscal Year and Fiscal Week. The table is
fully loaded with all possible Report dates, along with Year, The Fiscal year
and Fiscal week. I think it would be easier to get last years data by using
these fields in my querie. Any suggestions would be greatly appreciated.

Sincerely,

Chris

 

[strive4peace]

Hi Chris,

If you have 2 fields with the same name, you need to preface
them with the tablename when they are used in equations so
that Access knows where they come from

[tablename].[fieldname]


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

. So Sorry Do respond so late I was away. I did try it and it kept telling me
I was referencing the Field Report Date from more than one table. i tried
correcting this and it told me I did not have enought arguments. I would like
to say that the Week No is just a number and not a date field. Year and
Fiscal Year are numbers as well. The only date field I have is report date
which is the join between the Report Dates Table and Buyer Sales Table.
Thanks for your responses so far.

sincerely,

Chris

:

Hi Diggsy,

you can remove the VbSaturday argument -- it is optional and
just means that is the first day of the "week" for counting
-- normally, the first day is Sunday.

Did you try what I suggested?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

Thanks for replying. I just don't understand the the vb Saturday.All dates
happen to be a saturday that's all. Sorry for the confusion. I am showing you
an edited version of my Report Tables and my Sales table.As you can see I
would need to get the sales units for week 1 from feb 05,2005 and feb, 04
2006 and so forth.

Report Date Year Fiscal Year Week
05-Feb-05 2005 2006 1
12-Feb-05 2005 2006 2
19-Feb-05 2005 2006 3
04-Feb-06 2006 2007 1
11-Feb-06 2006 2007 2
18-Feb-06 2006 2007 3

Report Date Sales Units

05-Feb-05 4511
12-Feb-05 3724
19-Feb-05 2452
04-Feb-06 2878
11-Feb-06 3751
18-Feb-06 2982

Thank You

Chris



:



Hi Diggsy,

make these calculated fields in your query:

YW: format([datefield],"yy") &
format(DatePart("ww",[datefield],vbSaturday),"00")

for last year:
YprevW: format(DateAdd("y",-1,[datefield]),"yy") &
format(DatePart("ww",[datefield],vbSaturday),"00")

after you put these fields into your query, I think you will
see what to do... if not, post back

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com



Diggsy wrote:


Hello,
I have a DB that keeps Sales Records that are collected weekly(every Sat).
This is a mass amount of data with many records based on the week that they
are collected. I have a table that has the Report Date(the saturday
representing that week)and the sales units.. etc. I can run a querie that
gives me a total sum for that week. I also need the total sales from the same
week last year.
To help with my querie I created a table with Report Date( this is the join)
and added fields such as Year, Fiscal Year and Fiscal Week. The table is
fully loaded with all possible Report dates, along with Year, The Fiscal year
and Fiscal week. I think it would be easier to get last years data by using
these fields in my querie. Any suggestions would be greatly appreciated.

Sincerely,

Chris