Count based on multiple fields

[EZ]

I have a weekly production data. I'm trying to count the frequency of product
shipments. When Location B receives product from Location A. There are more
than 30 locations. The product consists of 3 parts: Product class, product
size, product flavor. There's also a field that concatenate all the 3 parts
together. Then there are: Shipping Loc, Receiving Loc, Quantity, etc. I have
another table that shows what location produces what product/s. First I need
to know the frequency (weekly) of Loc B receiving an "Item" (consider this
the concatenated product field - the 3 parts) from Loc A. The frequency is
one instance per week. Once we determine the count/frequency, then I'd like
to know if Loc B produced the "Item" or not. If No, the next question will be
does it produce the product class? the product size within the same class,
the flavor within the same class?. I have all my tables ready. How can I
apply the count? What's the best approach to add the "Yes", "No" fields to
the Product parts questions? Any help is greatly appreciated.

Thanks.

 

[vanderghast]

That sounds like the whole project, not *a* (one) query. And it sounds
fuzzy at many places (from the short explanations you gave). It is generally
better to be as specific as possible. As example, to know how many times
(count) there is a record telling that something left locA for locB with a
table with data like:

someID, someDate, fromLoc, toLoc , someProduct, quantity ' fields name

then a crosstab could do:

TRANSFORM Nz(COUNT(*), 0)
SELECT fromLoc
FROM tableName
WHERE someDate BETWEEN thisDate AND thatDate
GROUP BY fromLoc
PIVOT toLoc


giving, for each source (vertical) each destination (horizontal) how many
records there is in the table, for the given period.

To know the amount of products shipped, replace COUNT(*) by SUM(quantity)


Vanderghast, Access MVP

 

[EZ]

I think I managed to get the Count to work.

Now I need to create a new table out of my production table to show by
location who produces what. So I will query my prod tbl to get all the
locations, and the products they produces. There are 4 fields for the
'Product': ProdClass, ProdSize, ProdFlvr, and the 3 parts combined together.
In my new table, I'd like to have a "Yes"/"No" fields to show whether a Loc
makes "ProdClass", "ProdSize", "ProdFlvr", "ProdItem". ProdItem is the
combination of the 3 together. If a Loc makes a "ProdItem", then by default
the other 3 fields will show "Yes". I only need some directions on how to
approach this... lookup, etc, and I can finish it up myself. Thanks.

Thanks.

 

[vanderghast]

If your table is like:


ProductName Localisation ' fields
ice cream Boston
ice cream Seattle
minivan Detroit ' data sample


then


TRANSFORM 0 <> Nz(COUNT(*), 0)
SELECT localisation
FROM table
GROUP BY localisation
PIVOT productName


will list horizontally the products, by localisation (vertically) while


TRANSFORM 0 <> Nz(COUNT(*), 0)
SELECT productName
FROM table
GROUP BY productName
PIVOT localisation


will list horizontally the localisations, by products (vertically).

Note that

TRANSFORM Nz(COUNT(*), 0)
SELECT ...


will supply the number of localisation supplying the product name, rather
than just a Yes (-1) or No (0) as in the first two example.



Vanderghast, Access MVP

I think I managed to get the Count to work.

Now I need to create a new table out of my production table to show by
location who produces what. So I will query my prod tbl to get all the
locations, and the products they produces. There are 4 fields for the
'Product': ProdClass, ProdSize, ProdFlvr, and the 3 parts combined
together.
In my new table, I'd like to have a "Yes"/"No" fields to show whether a
Loc
makes "ProdClass", "ProdSize", "ProdFlvr", "ProdItem". ProdItem is the
combination of the 3 together. If a Loc makes a "ProdItem", then by
default
the other 3 fields will show "Yes". I only need some directions on how to
approach this... lookup, etc, and I can finish it up myself. Thanks.

Thanks.

 

[EZ]

Thanks, but I don't think this is what i'm after. I need to query two tables,
and in the new query, I need to match fields from one table with fields into
the second table and have 4 fields in the query as a "Y"/"N" fields depending
on the lookup conditions between the two tables. So my new query will show
fields from both tables in addition to new 4 fields with "Y" or "N" data type
so i can make a table of that query to use as my source for analyses. I hope
I clarified the problem better this time.

Thanks.