Query Criteria Help

S

sahafi

Hi,

I need some help setting up the right critera on my query. My data is
arranged by month, week, location information (4 fields), LBs. I have 11
months worth of data (44 weeks). I need to show the locations that stopped
ordering (zero LBs), last week they received order, number of weeks they
received orders (the first week they receive order not necessarily wk1 of
month1, it could be any wk).
When I used Less than <1 LBs, I get some locations with zero LBs, but I
can't tell how many weeks they have received orders, nor the last week they
did receive orders. We basically trying to investigate whether a location
still exist or not. Remember i'm dealing with massive volume of data. Any
help is appreciated.

Thanks.
 
J

Jeff Boyce

"How" depends on "what".

I don't have a very clear understanding of the data structure (and examples
of the data) you are using.

How to do the query depends on what data you have stored, and how it's
organized.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
S

sahafi

Hi Jeff,

All fields have text data type except for the last field "LBs" which's
number data type.
A location usually place an order once a week (but doesn't have to be every
week). So max orders per week per loc is 1. Min orders per loc per week is
zero.
You could have a value of [11]for the "month" field and [2] for the "Wk"
field, then there are Loc_Cd, Loc_Descp, Loc_Type, then the "LBs" field.
There's no starting order date nor an end order date. Most locations are
there since the begining of the year (month 1 Wk 1), but a few are new
locations that being added after wk1 of month1.
I need to show the number of weeks out of those 44 weeks each location has
orders of more than 1 LBs. Obviously the reverse of that is how many weeks a
loc had not received any orders. When was the first order (month/wk), when
was the last order received (month/wk). For example, if a loc didn't show any
LBs on the last 8 weeks (month 11 & 10), we need to find out whether to
remove that customer from the dB. It's easier to see this at the end, but not
as easy if a loc stopped ordering many weeks ago. This is a one time process,
once things get cleared going forward, whenever we bring one month data
(4wks) I can set up a criteria for just those 4 weeks and show loc with zero
LBs.

I hope it's clear now.

Thanks.
 
J

Jeff Boyce

I'm still having a bit of trouble "seeing" the structure.

Would you mind using something like the following example to describe your
table(s):

tblPerson
PersonID
FirstName
MiddleName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

Thanks.

Regards

Jeff Boyce
Microsoft Office/Access MVP


sahafi said:
Hi Jeff,

All fields have text data type except for the last field "LBs" which's
number data type.
A location usually place an order once a week (but doesn't have to be
every
week). So max orders per week per loc is 1. Min orders per loc per week is
zero.
You could have a value of [11]for the "month" field and [2] for the "Wk"
field, then there are Loc_Cd, Loc_Descp, Loc_Type, then the "LBs" field.
There's no starting order date nor an end order date. Most locations are
there since the begining of the year (month 1 Wk 1), but a few are new
locations that being added after wk1 of month1.
I need to show the number of weeks out of those 44 weeks each location has
orders of more than 1 LBs. Obviously the reverse of that is how many weeks
a
loc had not received any orders. When was the first order (month/wk), when
was the last order received (month/wk). For example, if a loc didn't show
any
LBs on the last 8 weeks (month 11 & 10), we need to find out whether to
remove that customer from the dB. It's easier to see this at the end, but
not
as easy if a loc stopped ordering many weeks ago. This is a one time
process,
once things get cleared going forward, whenever we bring one month data
(4wks) I can set up a criteria for just those 4 weeks and show loc with
zero
LBs.

I hope it's clear now.

Thanks.
--
when u change the way u look @ things, the things u look at change.


Jeff Boyce said:
"How" depends on "what".

I don't have a very clear understanding of the data structure (and
examples
of the data) you are using.

How to do the query depends on what data you have stored, and how it's
organized.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
S

sahafi

tblOrders
Period
Week
Ord_Loc_Cd
Ord_Loc_Descp
Ord_Loc_Type
LBs

Thanks.
--
when u change the way u look @ things, the things u look at change.


Jeff Boyce said:
I'm still having a bit of trouble "seeing" the structure.

Would you mind using something like the following example to describe your
table(s):

tblPerson
PersonID
FirstName
MiddleName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

Thanks.

Regards

Jeff Boyce
Microsoft Office/Access MVP


sahafi said:
Hi Jeff,

All fields have text data type except for the last field "LBs" which's
number data type.
A location usually place an order once a week (but doesn't have to be
every
week). So max orders per week per loc is 1. Min orders per loc per week is
zero.
You could have a value of [11]for the "month" field and [2] for the "Wk"
field, then there are Loc_Cd, Loc_Descp, Loc_Type, then the "LBs" field.
There's no starting order date nor an end order date. Most locations are
there since the begining of the year (month 1 Wk 1), but a few are new
locations that being added after wk1 of month1.
I need to show the number of weeks out of those 44 weeks each location has
orders of more than 1 LBs. Obviously the reverse of that is how many weeks
a
loc had not received any orders. When was the first order (month/wk), when
was the last order received (month/wk). For example, if a loc didn't show
any
LBs on the last 8 weeks (month 11 & 10), we need to find out whether to
remove that customer from the dB. It's easier to see this at the end, but
not
as easy if a loc stopped ordering many weeks ago. This is a one time
process,
once things get cleared going forward, whenever we bring one month data
(4wks) I can set up a criteria for just those 4 weeks and show loc with
zero
LBs.

I hope it's clear now.

Thanks.
--
when u change the way u look @ things, the things u look at change.


Jeff Boyce said:
"How" depends on "what".

I don't have a very clear understanding of the data structure (and
examples
of the data) you are using.

How to do the query depends on what data you have stored, and how it's
organized.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I need some help setting up the right critera on my query. My data is
arranged by month, week, location information (4 fields), LBs. I have
11
months worth of data (44 weeks). I need to show the locations that
stopped
ordering (zero LBs), last week they received order, number of weeks
they
received orders (the first week they receive order not necessarily wk1
of
month1, it could be any wk).
When I used Less than <1 LBs, I get some locations with zero LBs, but I
can't tell how many weeks they have received orders, nor the last week
they
did receive orders. We basically trying to investigate whether a
location
still exist or not. Remember i'm dealing with massive volume of data.
Any
help is appreciated.

Thanks.
 
J

Jeff Boyce

Your previous post mentioned a "month" field... is that the [Period] field
in tblOrders?

If you include an [Ord_Loc_Cd] (?order location code?), why are you also
including the [Ord_Loc_Descp] (?description?) and ...Type fields? Aren't
those redundant? That is, if you know the Cd, aren't the others already
known?

If you are recording a [month] and a [week], what do you do NEXT year when
you have the same month and week combinations again?

The various details you are trying to find may require more than one query.

For example, one query might be to use the Totals query, grouping by
?[Ord_Loc_Cd], by [month] and by [week].

For any given location (?[Ord_Loc_Cd]), you could look for the maximum week
(hmmm, then again, maybe not, if you are recording M1, W1, and M2, W2, and
.... To use the maximum approach re: dates, you'd need an actual date, not a
text value.

Does this get you started?

Regards

Jeff Boyce
Microsoft Office/Access MVP

sahafi said:
tblOrders
Period
Week
Ord_Loc_Cd
Ord_Loc_Descp
Ord_Loc_Type
LBs

Thanks.
--
when u change the way u look @ things, the things u look at change.


Jeff Boyce said:
I'm still having a bit of trouble "seeing" the structure.

Would you mind using something like the following example to describe
your
table(s):

tblPerson
PersonID
FirstName
MiddleName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

Thanks.

Regards

Jeff Boyce
Microsoft Office/Access MVP


sahafi said:
Hi Jeff,

All fields have text data type except for the last field "LBs" which's
number data type.
A location usually place an order once a week (but doesn't have to be
every
week). So max orders per week per loc is 1. Min orders per loc per week
is
zero.
You could have a value of [11]for the "month" field and [2] for the
"Wk"
field, then there are Loc_Cd, Loc_Descp, Loc_Type, then the "LBs"
field.
There's no starting order date nor an end order date. Most locations
are
there since the begining of the year (month 1 Wk 1), but a few are new
locations that being added after wk1 of month1.
I need to show the number of weeks out of those 44 weeks each location
has
orders of more than 1 LBs. Obviously the reverse of that is how many
weeks
a
loc had not received any orders. When was the first order (month/wk),
when
was the last order received (month/wk). For example, if a loc didn't
show
any
LBs on the last 8 weeks (month 11 & 10), we need to find out whether to
remove that customer from the dB. It's easier to see this at the end,
but
not
as easy if a loc stopped ordering many weeks ago. This is a one time
process,
once things get cleared going forward, whenever we bring one month data
(4wks) I can set up a criteria for just those 4 weeks and show loc with
zero
LBs.

I hope it's clear now.

Thanks.
--
when u change the way u look @ things, the things u look at change.


:

"How" depends on "what".

I don't have a very clear understanding of the data structure (and
examples
of the data) you are using.

How to do the query depends on what data you have stored, and how it's
organized.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I need some help setting up the right critera on my query. My data
is
arranged by month, week, location information (4 fields), LBs. I
have
11
months worth of data (44 weeks). I need to show the locations that
stopped
ordering (zero LBs), last week they received order, number of weeks
they
received orders (the first week they receive order not necessarily
wk1
of
month1, it could be any wk).
When I used Less than <1 LBs, I get some locations with zero LBs,
but I
can't tell how many weeks they have received orders, nor the last
week
they
did receive orders. We basically trying to investigate whether a
location
still exist or not. Remember i'm dealing with massive volume of
data.
Any
help is appreciated.

Thanks.
 
S

sahafi

Thanks.
Yes 'Period' is the same as "month".
The data is for one year. Next year will bring in new data.
The reason we include the descriptions, because the result of that query
going to be uploaded on modeling tool to determine orders volume per region
(so they need to be included). Yes both month(period) and week field are text
data type. The period values like: 01, 02, ...11. The week values like: 1, 2,
3, 4. We do not have control over that as they come from the corporate dB as
so.
As I stated earlier, this is a one time process. I'm just trying to avoid
doing this in Excel (too much data). I know it could be done, or done to some
extend, within the sql statement or VBA; The first Period/Wk combination a
loc has order greater than zero, count number of weeks the loc has orders
less than one LBs, etc. I just need a start up point and I can do the rest.

Thanks.

--
when u change the way u look @ things, the things u look at change.


Jeff Boyce said:
Your previous post mentioned a "month" field... is that the [Period] field
in tblOrders?

If you include an [Ord_Loc_Cd] (?order location code?), why are you also
including the [Ord_Loc_Descp] (?description?) and ...Type fields? Aren't
those redundant? That is, if you know the Cd, aren't the others already
known?

If you are recording a [month] and a [week], what do you do NEXT year when
you have the same month and week combinations again?

The various details you are trying to find may require more than one query.

For example, one query might be to use the Totals query, grouping by
?[Ord_Loc_Cd], by [month] and by [week].

For any given location (?[Ord_Loc_Cd]), you could look for the maximum week
(hmmm, then again, maybe not, if you are recording M1, W1, and M2, W2, and
.... To use the maximum approach re: dates, you'd need an actual date, not a
text value.

Does this get you started?

Regards

Jeff Boyce
Microsoft Office/Access MVP

sahafi said:
tblOrders
Period
Week
Ord_Loc_Cd
Ord_Loc_Descp
Ord_Loc_Type
LBs

Thanks.
--
when u change the way u look @ things, the things u look at change.


Jeff Boyce said:
I'm still having a bit of trouble "seeing" the structure.

Would you mind using something like the following example to describe
your
table(s):

tblPerson
PersonID
FirstName
MiddleName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

Thanks.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Jeff,

All fields have text data type except for the last field "LBs" which's
number data type.
A location usually place an order once a week (but doesn't have to be
every
week). So max orders per week per loc is 1. Min orders per loc per week
is
zero.
You could have a value of [11]for the "month" field and [2] for the
"Wk"
field, then there are Loc_Cd, Loc_Descp, Loc_Type, then the "LBs"
field.
There's no starting order date nor an end order date. Most locations
are
there since the begining of the year (month 1 Wk 1), but a few are new
locations that being added after wk1 of month1.
I need to show the number of weeks out of those 44 weeks each location
has
orders of more than 1 LBs. Obviously the reverse of that is how many
weeks
a
loc had not received any orders. When was the first order (month/wk),
when
was the last order received (month/wk). For example, if a loc didn't
show
any
LBs on the last 8 weeks (month 11 & 10), we need to find out whether to
remove that customer from the dB. It's easier to see this at the end,
but
not
as easy if a loc stopped ordering many weeks ago. This is a one time
process,
once things get cleared going forward, whenever we bring one month data
(4wks) I can set up a criteria for just those 4 weeks and show loc with
zero
LBs.

I hope it's clear now.

Thanks.
--
when u change the way u look @ things, the things u look at change.


:

"How" depends on "what".

I don't have a very clear understanding of the data structure (and
examples
of the data) you are using.

How to do the query depends on what data you have stored, and how it's
organized.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I need some help setting up the right critera on my query. My data
is
arranged by month, week, location information (4 fields), LBs. I
have
11
months worth of data (44 weeks). I need to show the locations that
stopped
ordering (zero LBs), last week they received order, number of weeks
they
received orders (the first week they receive order not necessarily
wk1
of
month1, it could be any wk).
When I used Less than <1 LBs, I get some locations with zero LBs,
but I
can't tell how many weeks they have received orders, nor the last
week
they
did receive orders. We basically trying to investigate whether a
location
still exist or not. Remember i'm dealing with massive volume of
data.
Any
help is appreciated.

Thanks.
 
J

Jeff Boyce

"First" depends on what you are measuring.

If you concatenate (in a query) the month and week, you'd have values like
"011", "012", "013", "014", "015", "021", ...

I suspect you could sort by that concatenated field to get the "most recent"
(i.e., the maximum).

It isn't clear if you have "dummy" records for a Loc that contains ??? when
there's NOTHING, or if you only have a record when there IS something.

You mention "orders", but I don't see any of the fields you mentioned that
look like they contain the number of orders (1 or 0).

I still don't have a good enough grasp of what you are starting with to
offer ideas about how to get what you are looking for.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

sahafi said:
Thanks.
Yes 'Period' is the same as "month".
The data is for one year. Next year will bring in new data.
The reason we include the descriptions, because the result of that query
going to be uploaded on modeling tool to determine orders volume per
region
(so they need to be included). Yes both month(period) and week field are
text
data type. The period values like: 01, 02, ...11. The week values like: 1,
2,
3, 4. We do not have control over that as they come from the corporate dB
as
so.
As I stated earlier, this is a one time process. I'm just trying to avoid
doing this in Excel (too much data). I know it could be done, or done to
some
extend, within the sql statement or VBA; The first Period/Wk combination a
loc has order greater than zero, count number of weeks the loc has orders
less than one LBs, etc. I just need a start up point and I can do the
rest.

Thanks.

--
when u change the way u look @ things, the things u look at change.


Jeff Boyce said:
Your previous post mentioned a "month" field... is that the [Period]
field
in tblOrders?

If you include an [Ord_Loc_Cd] (?order location code?), why are you also
including the [Ord_Loc_Descp] (?description?) and ...Type fields? Aren't
those redundant? That is, if you know the Cd, aren't the others already
known?

If you are recording a [month] and a [week], what do you do NEXT year
when
you have the same month and week combinations again?

The various details you are trying to find may require more than one
query.

For example, one query might be to use the Totals query, grouping by
?[Ord_Loc_Cd], by [month] and by [week].

For any given location (?[Ord_Loc_Cd]), you could look for the maximum
week
(hmmm, then again, maybe not, if you are recording M1, W1, and M2, W2,
and
.... To use the maximum approach re: dates, you'd need an actual date,
not a
text value.

Does this get you started?

Regards

Jeff Boyce
Microsoft Office/Access MVP

sahafi said:
tblOrders
Period
Week
Ord_Loc_Cd
Ord_Loc_Descp
Ord_Loc_Type
LBs

Thanks.
--
when u change the way u look @ things, the things u look at change.


:

I'm still having a bit of trouble "seeing" the structure.

Would you mind using something like the following example to describe
your
table(s):

tblPerson
PersonID
FirstName
MiddleName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

Thanks.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Jeff,

All fields have text data type except for the last field "LBs"
which's
number data type.
A location usually place an order once a week (but doesn't have to
be
every
week). So max orders per week per loc is 1. Min orders per loc per
week
is
zero.
You could have a value of [11]for the "month" field and [2] for the
"Wk"
field, then there are Loc_Cd, Loc_Descp, Loc_Type, then the "LBs"
field.
There's no starting order date nor an end order date. Most locations
are
there since the begining of the year (month 1 Wk 1), but a few are
new
locations that being added after wk1 of month1.
I need to show the number of weeks out of those 44 weeks each
location
has
orders of more than 1 LBs. Obviously the reverse of that is how many
weeks
a
loc had not received any orders. When was the first order
(month/wk),
when
was the last order received (month/wk). For example, if a loc didn't
show
any
LBs on the last 8 weeks (month 11 & 10), we need to find out whether
to
remove that customer from the dB. It's easier to see this at the
end,
but
not
as easy if a loc stopped ordering many weeks ago. This is a one time
process,
once things get cleared going forward, whenever we bring one month
data
(4wks) I can set up a criteria for just those 4 weeks and show loc
with
zero
LBs.

I hope it's clear now.

Thanks.
--
when u change the way u look @ things, the things u look at change.


:

"How" depends on "what".

I don't have a very clear understanding of the data structure (and
examples
of the data) you are using.

How to do the query depends on what data you have stored, and how
it's
organized.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I need some help setting up the right critera on my query. My
data
is
arranged by month, week, location information (4 fields), LBs. I
have
11
months worth of data (44 weeks). I need to show the locations
that
stopped
ordering (zero LBs), last week they received order, number of
weeks
they
received orders (the first week they receive order not
necessarily
wk1
of
month1, it could be any wk).
When I used Less than <1 LBs, I get some locations with zero LBs,
but I
can't tell how many weeks they have received orders, nor the last
week
they
did receive orders. We basically trying to investigate whether a
location
still exist or not. Remember i'm dealing with massive volume of
data.
Any
help is appreciated.

Thanks.
 
S

sahafi

Ya, it's a bit difficult to resolve. There's no orders field, but when the
value in the [LBs] pounds is greater than zero, that means a location have
ordered/received an order. When I mentioned the value on that same field
could be less than 1 (or zero... no LBs) that implies a location would be
listed regardless if they have orders or not! As I mentioned previously, the
main purpose to find out what locations have not ordered for several weeks,
so we can have them ready to be removed from the dB. Thanks for trying.
--
when u change the way u look @ things, the things u look at change.


Jeff Boyce said:
"First" depends on what you are measuring.

If you concatenate (in a query) the month and week, you'd have values like
"011", "012", "013", "014", "015", "021", ...

I suspect you could sort by that concatenated field to get the "most recent"
(i.e., the maximum).

It isn't clear if you have "dummy" records for a Loc that contains ??? when
there's NOTHING, or if you only have a record when there IS something.

You mention "orders", but I don't see any of the fields you mentioned that
look like they contain the number of orders (1 or 0).

I still don't have a good enough grasp of what you are starting with to
offer ideas about how to get what you are looking for.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

sahafi said:
Thanks.
Yes 'Period' is the same as "month".
The data is for one year. Next year will bring in new data.
The reason we include the descriptions, because the result of that query
going to be uploaded on modeling tool to determine orders volume per
region
(so they need to be included). Yes both month(period) and week field are
text
data type. The period values like: 01, 02, ...11. The week values like: 1,
2,
3, 4. We do not have control over that as they come from the corporate dB
as
so.
As I stated earlier, this is a one time process. I'm just trying to avoid
doing this in Excel (too much data). I know it could be done, or done to
some
extend, within the sql statement or VBA; The first Period/Wk combination a
loc has order greater than zero, count number of weeks the loc has orders
less than one LBs, etc. I just need a start up point and I can do the
rest.

Thanks.

--
when u change the way u look @ things, the things u look at change.


Jeff Boyce said:
Your previous post mentioned a "month" field... is that the [Period]
field
in tblOrders?

If you include an [Ord_Loc_Cd] (?order location code?), why are you also
including the [Ord_Loc_Descp] (?description?) and ...Type fields? Aren't
those redundant? That is, if you know the Cd, aren't the others already
known?

If you are recording a [month] and a [week], what do you do NEXT year
when
you have the same month and week combinations again?

The various details you are trying to find may require more than one
query.

For example, one query might be to use the Totals query, grouping by
?[Ord_Loc_Cd], by [month] and by [week].

For any given location (?[Ord_Loc_Cd]), you could look for the maximum
week
(hmmm, then again, maybe not, if you are recording M1, W1, and M2, W2,
and
.... To use the maximum approach re: dates, you'd need an actual date,
not a
text value.

Does this get you started?

Regards

Jeff Boyce
Microsoft Office/Access MVP

tblOrders
Period
Week
Ord_Loc_Cd
Ord_Loc_Descp
Ord_Loc_Type
LBs

Thanks.
--
when u change the way u look @ things, the things u look at change.


:

I'm still having a bit of trouble "seeing" the structure.

Would you mind using something like the following example to describe
your
table(s):

tblPerson
PersonID
FirstName
MiddleName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

Thanks.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Jeff,

All fields have text data type except for the last field "LBs"
which's
number data type.
A location usually place an order once a week (but doesn't have to
be
every
week). So max orders per week per loc is 1. Min orders per loc per
week
is
zero.
You could have a value of [11]for the "month" field and [2] for the
"Wk"
field, then there are Loc_Cd, Loc_Descp, Loc_Type, then the "LBs"
field.
There's no starting order date nor an end order date. Most locations
are
there since the begining of the year (month 1 Wk 1), but a few are
new
locations that being added after wk1 of month1.
I need to show the number of weeks out of those 44 weeks each
location
has
orders of more than 1 LBs. Obviously the reverse of that is how many
weeks
a
loc had not received any orders. When was the first order
(month/wk),
when
was the last order received (month/wk). For example, if a loc didn't
show
any
LBs on the last 8 weeks (month 11 & 10), we need to find out whether
to
remove that customer from the dB. It's easier to see this at the
end,
but
not
as easy if a loc stopped ordering many weeks ago. This is a one time
process,
once things get cleared going forward, whenever we bring one month
data
(4wks) I can set up a criteria for just those 4 weeks and show loc
with
zero
LBs.

I hope it's clear now.

Thanks.
--
when u change the way u look @ things, the things u look at change.


:

"How" depends on "what".

I don't have a very clear understanding of the data structure (and
examples
of the data) you are using.

How to do the query depends on what data you have stored, and how
it's
organized.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I need some help setting up the right critera on my query. My
data
is
arranged by month, week, location information (4 fields), LBs. I
have
11
months worth of data (44 weeks). I need to show the locations
that
stopped
ordering (zero LBs), last week they received order, number of
weeks
they
received orders (the first week they receive order not
necessarily
wk1
of
month1, it could be any wk).
When I used Less than <1 LBs, I get some locations with zero LBs,
but I
can't tell how many weeks they have received orders, nor the last
week
they
did receive orders. We basically trying to investigate whether a
location
still exist or not. Remember i'm dealing with massive volume of
data.
Any
help is appreciated.

Thanks.
 
J

Jeff Boyce

So the [LBs] field is being used for both "ordering" and "receiving"? How
do you know whether the Loc has ordered or received?

I'm still having trouble seeing how you can "get there from here", given the
limited fields you've allowed yourself.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


sahafi said:
Ya, it's a bit difficult to resolve. There's no orders field, but when the
value in the [LBs] pounds is greater than zero, that means a location have
ordered/received an order. When I mentioned the value on that same field
could be less than 1 (or zero... no LBs) that implies a location would be
listed regardless if they have orders or not! As I mentioned previously,
the
main purpose to find out what locations have not ordered for several
weeks,
so we can have them ready to be removed from the dB. Thanks for trying.
--
when u change the way u look @ things, the things u look at change.


Jeff Boyce said:
"First" depends on what you are measuring.

If you concatenate (in a query) the month and week, you'd have values
like
"011", "012", "013", "014", "015", "021", ...

I suspect you could sort by that concatenated field to get the "most
recent"
(i.e., the maximum).

It isn't clear if you have "dummy" records for a Loc that contains ???
when
there's NOTHING, or if you only have a record when there IS something.

You mention "orders", but I don't see any of the fields you mentioned
that
look like they contain the number of orders (1 or 0).

I still don't have a good enough grasp of what you are starting with to
offer ideas about how to get what you are looking for.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

sahafi said:
Thanks.
Yes 'Period' is the same as "month".
The data is for one year. Next year will bring in new data.
The reason we include the descriptions, because the result of that
query
going to be uploaded on modeling tool to determine orders volume per
region
(so they need to be included). Yes both month(period) and week field
are
text
data type. The period values like: 01, 02, ...11. The week values like:
1,
2,
3, 4. We do not have control over that as they come from the corporate
dB
as
so.
As I stated earlier, this is a one time process. I'm just trying to
avoid
doing this in Excel (too much data). I know it could be done, or done
to
some
extend, within the sql statement or VBA; The first Period/Wk
combination a
loc has order greater than zero, count number of weeks the loc has
orders
less than one LBs, etc. I just need a start up point and I can do the
rest.

Thanks.

--
when u change the way u look @ things, the things u look at change.


:

Your previous post mentioned a "month" field... is that the [Period]
field
in tblOrders?

If you include an [Ord_Loc_Cd] (?order location code?), why are you
also
including the [Ord_Loc_Descp] (?description?) and ...Type fields?
Aren't
those redundant? That is, if you know the Cd, aren't the others
already
known?

If you are recording a [month] and a [week], what do you do NEXT year
when
you have the same month and week combinations again?

The various details you are trying to find may require more than one
query.

For example, one query might be to use the Totals query, grouping by
?[Ord_Loc_Cd], by [month] and by [week].

For any given location (?[Ord_Loc_Cd]), you could look for the maximum
week
(hmmm, then again, maybe not, if you are recording M1, W1, and M2, W2,
and
.... To use the maximum approach re: dates, you'd need an actual
date,
not a
text value.

Does this get you started?

Regards

Jeff Boyce
Microsoft Office/Access MVP

tblOrders
Period
Week
Ord_Loc_Cd
Ord_Loc_Descp
Ord_Loc_Type
LBs

Thanks.
--
when u change the way u look @ things, the things u look at change.


:

I'm still having a bit of trouble "seeing" the structure.

Would you mind using something like the following example to
describe
your
table(s):

tblPerson
PersonID
FirstName
MiddleName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

Thanks.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Jeff,

All fields have text data type except for the last field "LBs"
which's
number data type.
A location usually place an order once a week (but doesn't have
to
be
every
week). So max orders per week per loc is 1. Min orders per loc
per
week
is
zero.
You could have a value of [11]for the "month" field and [2] for
the
"Wk"
field, then there are Loc_Cd, Loc_Descp, Loc_Type, then the "LBs"
field.
There's no starting order date nor an end order date. Most
locations
are
there since the begining of the year (month 1 Wk 1), but a few
are
new
locations that being added after wk1 of month1.
I need to show the number of weeks out of those 44 weeks each
location
has
orders of more than 1 LBs. Obviously the reverse of that is how
many
weeks
a
loc had not received any orders. When was the first order
(month/wk),
when
was the last order received (month/wk). For example, if a loc
didn't
show
any
LBs on the last 8 weeks (month 11 & 10), we need to find out
whether
to
remove that customer from the dB. It's easier to see this at the
end,
but
not
as easy if a loc stopped ordering many weeks ago. This is a one
time
process,
once things get cleared going forward, whenever we bring one
month
data
(4wks) I can set up a criteria for just those 4 weeks and show
loc
with
zero
LBs.

I hope it's clear now.

Thanks.
--
when u change the way u look @ things, the things u look at
change.


:

"How" depends on "what".

I don't have a very clear understanding of the data structure
(and
examples
of the data) you are using.

How to do the query depends on what data you have stored, and
how
it's
organized.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I need some help setting up the right critera on my query. My
data
is
arranged by month, week, location information (4 fields), LBs.
I
have
11
months worth of data (44 weeks). I need to show the locations
that
stopped
ordering (zero LBs), last week they received order, number of
weeks
they
received orders (the first week they receive order not
necessarily
wk1
of
month1, it could be any wk).
When I used Less than <1 LBs, I get some locations with zero
LBs,
but I
can't tell how many weeks they have received orders, nor the
last
week
they
did receive orders. We basically trying to investigate whether
a
location
still exist or not. Remember i'm dealing with massive volume
of
data.
Any
help is appreciated.

Thanks.
 
S

sahafi

There's another part of the data about the 'Origin'. I'm only concerned about
the destination. This is basically shipment data. If a location received an
order (meaning the value in LBs is greater than zero), that implies the order
had been placed already. I think you are asking questions that of no relevant
to the issue at hand. It really doesn't matter how they place their order.
Let me rephrase the issue: How many weeks each location received more than
zero LBs?
When was the first time (period/wk) each loc received more than zero LBs?
When was the last time (Period/wk) each loc received more than zero LBs?

I hope it makes sense now.

Thanks.
--
when u change the way u look @ things, the things u look at change.


Jeff Boyce said:
So the [LBs] field is being used for both "ordering" and "receiving"? How
do you know whether the Loc has ordered or received?

I'm still having trouble seeing how you can "get there from here", given the
limited fields you've allowed yourself.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


sahafi said:
Ya, it's a bit difficult to resolve. There's no orders field, but when the
value in the [LBs] pounds is greater than zero, that means a location have
ordered/received an order. When I mentioned the value on that same field
could be less than 1 (or zero... no LBs) that implies a location would be
listed regardless if they have orders or not! As I mentioned previously,
the
main purpose to find out what locations have not ordered for several
weeks,
so we can have them ready to be removed from the dB. Thanks for trying.
--
when u change the way u look @ things, the things u look at change.


Jeff Boyce said:
"First" depends on what you are measuring.

If you concatenate (in a query) the month and week, you'd have values
like
"011", "012", "013", "014", "015", "021", ...

I suspect you could sort by that concatenated field to get the "most
recent"
(i.e., the maximum).

It isn't clear if you have "dummy" records for a Loc that contains ???
when
there's NOTHING, or if you only have a record when there IS something.

You mention "orders", but I don't see any of the fields you mentioned
that
look like they contain the number of orders (1 or 0).

I still don't have a good enough grasp of what you are starting with to
offer ideas about how to get what you are looking for.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks.
Yes 'Period' is the same as "month".
The data is for one year. Next year will bring in new data.
The reason we include the descriptions, because the result of that
query
going to be uploaded on modeling tool to determine orders volume per
region
(so they need to be included). Yes both month(period) and week field
are
text
data type. The period values like: 01, 02, ...11. The week values like:
1,
2,
3, 4. We do not have control over that as they come from the corporate
dB
as
so.
As I stated earlier, this is a one time process. I'm just trying to
avoid
doing this in Excel (too much data). I know it could be done, or done
to
some
extend, within the sql statement or VBA; The first Period/Wk
combination a
loc has order greater than zero, count number of weeks the loc has
orders
less than one LBs, etc. I just need a start up point and I can do the
rest.

Thanks.

--
when u change the way u look @ things, the things u look at change.


:

Your previous post mentioned a "month" field... is that the [Period]
field
in tblOrders?

If you include an [Ord_Loc_Cd] (?order location code?), why are you
also
including the [Ord_Loc_Descp] (?description?) and ...Type fields?
Aren't
those redundant? That is, if you know the Cd, aren't the others
already
known?

If you are recording a [month] and a [week], what do you do NEXT year
when
you have the same month and week combinations again?

The various details you are trying to find may require more than one
query.

For example, one query might be to use the Totals query, grouping by
?[Ord_Loc_Cd], by [month] and by [week].

For any given location (?[Ord_Loc_Cd]), you could look for the maximum
week
(hmmm, then again, maybe not, if you are recording M1, W1, and M2, W2,
and
.... To use the maximum approach re: dates, you'd need an actual
date,
not a
text value.

Does this get you started?

Regards

Jeff Boyce
Microsoft Office/Access MVP

tblOrders
Period
Week
Ord_Loc_Cd
Ord_Loc_Descp
Ord_Loc_Type
LBs

Thanks.
--
when u change the way u look @ things, the things u look at change.


:

I'm still having a bit of trouble "seeing" the structure.

Would you mind using something like the following example to
describe
your
table(s):

tblPerson
PersonID
FirstName
MiddleName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

Thanks.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Jeff,

All fields have text data type except for the last field "LBs"
which's
number data type.
A location usually place an order once a week (but doesn't have
to
be
every
week). So max orders per week per loc is 1. Min orders per loc
per
week
is
zero.
You could have a value of [11]for the "month" field and [2] for
the
"Wk"
field, then there are Loc_Cd, Loc_Descp, Loc_Type, then the "LBs"
field.
There's no starting order date nor an end order date. Most
locations
are
there since the begining of the year (month 1 Wk 1), but a few
are
new
locations that being added after wk1 of month1.
I need to show the number of weeks out of those 44 weeks each
location
has
orders of more than 1 LBs. Obviously the reverse of that is how
many
weeks
a
loc had not received any orders. When was the first order
(month/wk),
when
was the last order received (month/wk). For example, if a loc
didn't
show
any
LBs on the last 8 weeks (month 11 & 10), we need to find out
whether
to
remove that customer from the dB. It's easier to see this at the
end,
but
not
as easy if a loc stopped ordering many weeks ago. This is a one
time
process,
once things get cleared going forward, whenever we bring one
month
data
(4wks) I can set up a criteria for just those 4 weeks and show
loc
with
zero
LBs.

I hope it's clear now.

Thanks.
--
when u change the way u look @ things, the things u look at
change.


:

"How" depends on "what".

I don't have a very clear understanding of the data structure
(and
examples
of the data) you are using.

How to do the query depends on what data you have stored, and
how
it's
organized.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I need some help setting up the right critera on my query. My
data
is
arranged by month, week, location information (4 fields), LBs.
I
have
11
months worth of data (44 weeks). I need to show the locations
that
stopped
ordering (zero LBs), last week they received order, number of
weeks
they
received orders (the first week they receive order not
necessarily
wk1
of
month1, it could be any wk).
When I used Less than <1 LBs, I get some locations with zero
LBs,
but I
can't tell how many weeks they have received orders, nor the
 
J

Jeff Boyce

Take a look at the Totals query.

"How many" questions can be answered with the "Count" aggregation of a
Totals query.

"First" questions can be answered using the "Mininum" aggregation (see
previous responses - group by Loc, Minimum month+week).

"Last" questions answered using "Maximum" ...

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

sahafi said:
There's another part of the data about the 'Origin'. I'm only concerned
about
the destination. This is basically shipment data. If a location received
an
order (meaning the value in LBs is greater than zero), that implies the
order
had been placed already. I think you are asking questions that of no
relevant
to the issue at hand. It really doesn't matter how they place their order.
Let me rephrase the issue: How many weeks each location received more than
zero LBs?
When was the first time (period/wk) each loc received more than zero LBs?
When was the last time (Period/wk) each loc received more than zero LBs?

I hope it makes sense now.

Thanks.
--
when u change the way u look @ things, the things u look at change.


Jeff Boyce said:
So the [LBs] field is being used for both "ordering" and "receiving"?
How
do you know whether the Loc has ordered or received?

I'm still having trouble seeing how you can "get there from here", given
the
limited fields you've allowed yourself.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


sahafi said:
Ya, it's a bit difficult to resolve. There's no orders field, but when
the
value in the [LBs] pounds is greater than zero, that means a location
have
ordered/received an order. When I mentioned the value on that same
field
could be less than 1 (or zero... no LBs) that implies a location would
be
listed regardless if they have orders or not! As I mentioned
previously,
the
main purpose to find out what locations have not ordered for several
weeks,
so we can have them ready to be removed from the dB. Thanks for trying.
--
when u change the way u look @ things, the things u look at change.


:

"First" depends on what you are measuring.

If you concatenate (in a query) the month and week, you'd have values
like
"011", "012", "013", "014", "015", "021", ...

I suspect you could sort by that concatenated field to get the "most
recent"
(i.e., the maximum).

It isn't clear if you have "dummy" records for a Loc that contains ???
when
there's NOTHING, or if you only have a record when there IS something.

You mention "orders", but I don't see any of the fields you mentioned
that
look like they contain the number of orders (1 or 0).

I still don't have a good enough grasp of what you are starting with
to
offer ideas about how to get what you are looking for.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks.
Yes 'Period' is the same as "month".
The data is for one year. Next year will bring in new data.
The reason we include the descriptions, because the result of that
query
going to be uploaded on modeling tool to determine orders volume per
region
(so they need to be included). Yes both month(period) and week field
are
text
data type. The period values like: 01, 02, ...11. The week values
like:
1,
2,
3, 4. We do not have control over that as they come from the
corporate
dB
as
so.
As I stated earlier, this is a one time process. I'm just trying to
avoid
doing this in Excel (too much data). I know it could be done, or
done
to
some
extend, within the sql statement or VBA; The first Period/Wk
combination a
loc has order greater than zero, count number of weeks the loc has
orders
less than one LBs, etc. I just need a start up point and I can do
the
rest.

Thanks.

--
when u change the way u look @ things, the things u look at change.


:

Your previous post mentioned a "month" field... is that the
[Period]
field
in tblOrders?

If you include an [Ord_Loc_Cd] (?order location code?), why are you
also
including the [Ord_Loc_Descp] (?description?) and ...Type fields?
Aren't
those redundant? That is, if you know the Cd, aren't the others
already
known?

If you are recording a [month] and a [week], what do you do NEXT
year
when
you have the same month and week combinations again?

The various details you are trying to find may require more than
one
query.

For example, one query might be to use the Totals query, grouping
by
?[Ord_Loc_Cd], by [month] and by [week].

For any given location (?[Ord_Loc_Cd]), you could look for the
maximum
week
(hmmm, then again, maybe not, if you are recording M1, W1, and M2,
W2,
and
.... To use the maximum approach re: dates, you'd need an actual
date,
not a
text value.

Does this get you started?

Regards

Jeff Boyce
Microsoft Office/Access MVP

tblOrders
Period
Week
Ord_Loc_Cd
Ord_Loc_Descp
Ord_Loc_Type
LBs

Thanks.
--
when u change the way u look @ things, the things u look at
change.


:

I'm still having a bit of trouble "seeing" the structure.

Would you mind using something like the following example to
describe
your
table(s):

tblPerson
PersonID
FirstName
MiddleName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

Thanks.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Jeff,

All fields have text data type except for the last field "LBs"
which's
number data type.
A location usually place an order once a week (but doesn't
have
to
be
every
week). So max orders per week per loc is 1. Min orders per loc
per
week
is
zero.
You could have a value of [11]for the "month" field and [2]
for
the
"Wk"
field, then there are Loc_Cd, Loc_Descp, Loc_Type, then the
"LBs"
field.
There's no starting order date nor an end order date. Most
locations
are
there since the begining of the year (month 1 Wk 1), but a few
are
new
locations that being added after wk1 of month1.
I need to show the number of weeks out of those 44 weeks each
location
has
orders of more than 1 LBs. Obviously the reverse of that is
how
many
weeks
a
loc had not received any orders. When was the first order
(month/wk),
when
was the last order received (month/wk). For example, if a loc
didn't
show
any
LBs on the last 8 weeks (month 11 & 10), we need to find out
whether
to
remove that customer from the dB. It's easier to see this at
the
end,
but
not
as easy if a loc stopped ordering many weeks ago. This is a
one
time
process,
once things get cleared going forward, whenever we bring one
month
data
(4wks) I can set up a criteria for just those 4 weeks and show
loc
with
zero
LBs.

I hope it's clear now.

Thanks.
--
when u change the way u look @ things, the things u look at
change.


:

"How" depends on "what".

I don't have a very clear understanding of the data structure
(and
examples
of the data) you are using.

How to do the query depends on what data you have stored, and
how
it's
organized.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I need some help setting up the right critera on my query.
My
data
is
arranged by month, week, location information (4 fields),
LBs.
I
have
11
months worth of data (44 weeks). I need to show the
locations
that
stopped
ordering (zero LBs), last week they received order, number
of
weeks
they
received orders (the first week they receive order not
necessarily
wk1
of
month1, it could be any wk).
When I used Less than <1 LBs, I get some locations with
zero
LBs,
but I
can't tell how many weeks they have received orders, nor
the
 

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

Top