Breaking down dates for a query

[Guest]

Hi there.

Quick Intro:
I am creating a non-conformance reporting system for my company and I seem
to be stuck. (By the way a non-conformance report is a form that is filled
out when a customer sends back a part that has been damaged or built
incorrectly ( I work in aerospace manufacturing)). Anyways, so everytime
someone on the floor messes up a date, a dollar amt , and their name is
attributed to their mess up in an access form.

Question:
Each time an NCR is filled in a form in Access, a singular date is put in.
But for reporting purposes, I want to break the month down into weeks. I want
the week as the top 5 fields, the employee name on the left, and the dollar
amt in the grid between them. The problem is that I dont know how to break
the date down like that into 5 fields because it is only in there now as 1
field: the date. I'm sure there's a simple answer.

But...to complicate things, I want to prompt the user with a textbox asking
what month and year they are looking for. When they enter it in, it should be
all laid out for them. I know how to do text box prompts, but I dont know how
to combo that with breaking down the date into weeks. Please help I am a
student on a workterm and they are in awe of the results that i've already
gotten, so i dont want to drop the ball! Thank you so much, I'm glad I found
this place.

 

[Roger Carlson]

It sounds like you are talking about a CrossTab Query. There is a Wizard in
the Query Builder that will help. Play around with it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thank you Roger,

But still, how would I be able to break them down by the week instead of
a specific date? I have gone through the crosstab wizard and it allows me
fixed dates, or by quarter, or by year etc.

Also, I want all the names to still show even if there is no dollar value
within the table for them. Is that possible?

Thanks for your help, i am on the way to recovery.

 

[Roger Carlson]

You can break it down by week just like by quarter and year. They use the
Format function for quarter.
Something like "Qtr " & Format([TheDate],"q")

change it to:

"Week" & Format([TheDate],"w")

As for showing blank lines for people that don't have data, I don't think
that's possible.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Hi Roger,
Thanks again. I plunked in "Week" & Format([TheDate],"w") where the Qtr
function was, but it said that the database jet couldn't recognize
'[TheDate]" as a valid field name or expression. Hmmmm..

You can break it down by week just like by quarter and year. They use the
Format function for quarter.
Something like "Qtr " & Format([TheDate],"q")

change it to:

"Week" & Format([TheDate],"w")

As for showing blank lines for people that don't have data, I don't think
that's possible.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thank you Roger,

But still, how would I be able to break them down by the week instead of
a specific date? I have gone through the crosstab wizard and it allows me
fixed dates, or by quarter, or by year etc.

Also, I want all the names to still show even if there is no dollar value
within the table for them. Is that possible?

Thanks for your help, i am on the way to recovery.

 

[Roger Carlson]

I only used [TheDate] because you never gave the name of the date field.
Just replace the actual date field name for [TheDate].

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi Roger,
Thanks again. I plunked in "Week" & Format([TheDate],"w") where the Qtr
function was, but it said that the database jet couldn't recognize
'[TheDate]" as a valid field name or expression. Hmmmm..

You can break it down by week just like by quarter and year. They use the
Format function for quarter.
Something like "Qtr " & Format([TheDate],"q")

change it to:

"Week" & Format([TheDate],"w")

As for showing blank lines for people that don't have data, I don't think
that's possible.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thank you Roger,

But still, how would I be able to break them down by the week

instead
of

a specific date? I have gone through the crosstab wizard and it allows me
fixed dates, or by quarter, or by year etc.

Also, I want all the names to still show even if there is no dollar value
within the table for them. Is that possible?

Thanks for your help, i am on the way to recovery.

:

Hi there.

Quick Intro:
I am creating a non-conformance reporting system for my company and

I
seem

to be stuck. (By the way a non-conformance report is a form that is filled
out when a customer sends back a part that has been damaged or built
incorrectly ( I work in aerospace manufacturing)). Anyways, so everytime
someone on the floor messes up a date, a dollar amt , and their name is
attributed to their mess up in an access form.

Question:
Each time an NCR is filled in a form in Access, a singular date is

put
in.

But for reporting purposes, I want to break the month down into

weeks. I
want

the week as the top 5 fields, the employee name on the left, and the dollar
amt in the grid between them. The problem is that I dont know how to break
the date down like that into 5 fields because it is only in there

now as
1

field: the date. I'm sure there's a simple answer.

But...to complicate things, I want to prompt the user with a textbox asking
what month and year they are looking for. When they enter it in, it should be
all laid out for them. I know how to do text box prompts, but I dont know how
to combo that with breaking down the date into weeks. Please help I am a
student on a workterm and they are in awe of the results that i've already
gotten, so i dont want to drop the ball! Thank you so much, I'm glad

I
found

this place.