how to query values from different fields using different criteria?

[I_was_here]

Hey if anyone is a query pro please showoff some knowledge thx.

Ie: I have a table with :
part
price
location
qty

1 part repeats throughout the table and its price remains the same but
it has multiple locations so I'll have entries like:
cbl201 $0.28 STK1 11
cbl201 $0.28 STK2 13
cbl201 $0.28 STK8 8
cbl201 $0.28 STK11 6
I just want the price and different stock amounts.
But, a regular query will have duplicates and I want the result to
look like
Part: cb1201
Price: $0.28
Stock1:11
Stock2:13
Stock3:8
Stock11:6
I've tried several things to no avail. What may be helpful is that
there is a record of everything in STK1 so I limited my query with
criteria stock_id=STK1
In order to avoid duplicates. But I still want to know the qty stock
for the other ones so I created a new field and in the critera wrote
=[qty_stock] where [stock_id]="STK2" and so on for 3 and 11.. but it's
like I cannot create separate criteria for separate fields or
something. So I tried to put quieries together and I got the
error"SQL statement could not be exe because it contains ambigious
outer joins"

[Gary Floam]

For this you will need a report, which can eliminate the duplicates, not
just a query.

Gary
"I_was_here " <ks*****@yahoo. com> wrote in message
news:58******** *************** ***@posting.goo gle.com...

Hey if anyone is a query pro please showoff some knowledge thx.

Ie: I have a table with :
part
price
location
qty

1 part repeats throughout the table and its price remains the same but
it has multiple locations so I'll have entries like:
cbl201 $0.28 STK1 11
cbl201 $0.28 STK2 13
cbl201 $0.28 STK8 8
cbl201 $0.28 STK11 6
I just want the price and different stock amounts.
But, a regular query will have duplicates and I want the result to
look like
Part: cb1201
Price: $0.28
Stock1:11
Stock2:13
Stock3:8
Stock11:6
I've tried several things to no avail. What may be helpful is that
there is a record of everything in STK1 so I limited my query with
criteria stock_id=STK1
In order to avoid duplicates. But I still want to know the qty stock
for the other ones so I created a new field and in the critera wrote
=[qty_stock] where [stock_id]="STK2" and so on for 3 and 11.. but it's
like I cannot create separate criteria for separate fields or
something. So I tried to put quieries together and I got the
error"SQL statement could not be exe because it contains ambigious
outer joins"

[Don Leverton]

Hi,

Well my friend, you have just discovered the reason why *relational*
databases are so much better than *flat-file* databases. <grin>

What you need, IMHO, is 2 *related* tables... (hence the term "relational ")
tblPartsInvento ry -- which store info on the part itself, and
tblLocation -- which stores information on bin locations and quantities.

tblPartsInvento ry
- PartID - PK - AutoNumber ("PK" = Primary Key)
- PartNumber
- Description
- Price(s) ... (i.e. Retail ... Cost ... Core)
.... etc

tblLocation
- LocID - PK - AutoNumber
- PartID - FK - Long Integer ("FK" = Foreign Key)
- Location
- Qty
- CountDate
.... etc

In the relationships window, join the 2 tables (One-to-Many) on PartID

When you get all of that done, I think you'd have to create a cross-tab
query to give you the results that I think you are looking for. (I hate
crosstabs... I think this is right, but I'm not positive.)

TRANSFORM Sum(tblLocation .Qty) AS SumOfQty
SELECT tblPartsInvento ry.Line, tblPartsInvento ry.PartNumber,
tblPartsInvento ry.Description, tblPartsInvento ry.Retail,
tblPartsInvento ry.Cost, tblLocation.Loc ation
FROM tblPartsInvento ry INNER JOIN tblLocation ON tblPartsInvento ry.PartID =
tblLocation.Par tID
GROUP BY tblPartsInvento ry.Line, tblPartsInvento ry.PartNumber,
tblPartsInvento ry.Description, tblPartsInvento ry.Retail,
tblPartsInvento ry.Cost, tblLocation.Loc ation
ORDER BY tblPartsInvento ry.Line, tblPartsInvento ry.PartNumber
PIVOT tblLocation.Cou ntDate;

The results (for sure) could be displayed the way you want them using a
mainform / subform or in a report using grouping.

HTH,
Don
------------------------------------------------------------
I_was_here <ks*****@yahoo. com> wrote in message
news:58******** *************** ***@posting.goo gle.com...

Hey if anyone is a query pro please showoff some knowledge thx.

Ie: I have a table with :
part
price
location
qty

1 part repeats throughout the table and its price remains the same but
it has multiple locations so I'll have entries like:
cbl201 $0.28 STK1 11
cbl201 $0.28 STK2 13
cbl201 $0.28 STK8 8
cbl201 $0.28 STK11 6
I just want the price and different stock amounts.
But, a regular query will have duplicates and I want the result to
look like
Part: cb1201
Price: $0.28
Stock1:11
Stock2:13
Stock3:8
Stock11:6
I've tried several things to no avail. What may be helpful is that
there is a record of everything in STK1 so I limited my query with
criteria stock_id=STK1
In order to avoid duplicates. But I still want to know the qty stock
for the other ones so I created a new field and in the critera wrote
=[qty_stock] where [stock_id]="STK2" and so on for 3 and 11.. but it's
like I cannot create separate criteria for separate fields or
something. So I tried to put quieries together and I got the
error"SQL statement could not be exe because it contains ambigious
outer joins"

[I_was_here]

Hello Don!!

Thanks for the advice. I forgot to mention in my original post though
that I am trying to avoid creating another table as this is already a
sub table and I have around 5 other tables/queries relying on my
original table so there would be a huge maintenance impact from
changing it. Anyway I see where you were going with the joining of
the tables etc.. but I'm keeping it as one separate table ;) ;). I
decided to create a query that found a unique price and then relating
that to another query. It's not exactly what I wanted but it's
getting me the numbers I need. Merci beaucoup for your time!!!
"Don Leverton" <My*****@Telus. Net> wrote in message news:<86_Cc.165 2$_5.1137@clgrp s13>...

Hi,

Well my friend, you have just discovered the reason why *relational*
databases are so much better than *flat-file* databases. <grin>

What you need, IMHO, is 2 *related* tables... (hence the term "relational ")
tblPartsInvento ry -- which store info on the part itself, and
tblLocation -- which stores information on bin locations and quantities.

tblPartsInvento ry
- PartID - PK - AutoNumber ("PK" = Primary Key)
- PartNumber
- Description
- Price(s) ... (i.e. Retail ... Cost ... Core)
... etc

tblLocation
- LocID - PK - AutoNumber
- PartID - FK - Long Integer ("FK" = Foreign Key)
- Location
- Qty
- CountDate
... etc

In the relationships window, join the 2 tables (One-to-Many) on PartID

When you get all of that done, I think you'd have to create a cross-tab
query to give you the results that I think you are looking for. (I hate
crosstabs... I think this is right, but I'm not positive.)

TRANSFORM Sum(tblLocation .Qty) AS SumOfQty
SELECT tblPartsInvento ry.Line, tblPartsInvento ry.PartNumber,
tblPartsInvento ry.Description, tblPartsInvento ry.Retail,
tblPartsInvento ry.Cost, tblLocation.Loc ation
FROM tblPartsInvento ry INNER JOIN tblLocation ON tblPartsInvento ry.PartID =
tblLocation.Par tID
GROUP BY tblPartsInvento ry.Line, tblPartsInvento ry.PartNumber,
tblPartsInvento ry.Description, tblPartsInvento ry.Retail,
tblPartsInvento ry.Cost, tblLocation.Loc ation
ORDER BY tblPartsInvento ry.Line, tblPartsInvento ry.PartNumber
PIVOT tblLocation.Cou ntDate;

The results (for sure) could be displayed the way you want them using a
mainform / subform or in a report using grouping.

HTH,
Don
------------------------------------------------------------
I_was_here <ks*****@yahoo. com> wrote in message
news:58******** *************** ***@posting.goo gle.com...

Hey if anyone is a query pro please showoff some knowledge thx.

Ie: I have a table with :
part
price
location
qty

1 part repeats throughout the table and its price remains the same but
it has multiple locations so I'll have entries like:
cbl201 $0.28 STK1 11
cbl201 $0.28 STK2 13
cbl201 $0.28 STK8 8
cbl201 $0.28 STK11 6
I just want the price and different stock amounts.
But, a regular query will have duplicates and I want the result to
look like
Part: cb1201
Price: $0.28
Stock1:11
Stock2:13
Stock3:8
Stock11:6
I've tried several things to no avail. What may be helpful is that
there is a record of everything in STK1 so I limited my query with
criteria stock_id=STK1
In order to avoid duplicates. But I still want to know the qty stock
for the other ones so I created a new field and in the critera wrote
=[qty_stock] where [stock_id]="STK2" and so on for 3 and 11.. but it's
like I cannot create separate criteria for separate fields or
something. So I tried to put quieries together and I got the
error"SQL statement could not be exe because it contains ambigious
outer joins"