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
