By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

how to query values from different fields using different criteria?

P: n/a
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"
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.google.c om...
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"

Nov 13 '05 #2

P: n/a
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")
tblPartsInventory -- which store info on the part itself, and
tblLocation -- which stores information on bin locations and quantities.

tblPartsInventory
- 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 tblPartsInventory.Line, tblPartsInventory.PartNumber,
tblPartsInventory.Description, tblPartsInventory.Retail,
tblPartsInventory.Cost, tblLocation.Location
FROM tblPartsInventory INNER JOIN tblLocation ON tblPartsInventory.PartID =
tblLocation.PartID
GROUP BY tblPartsInventory.Line, tblPartsInventory.PartNumber,
tblPartsInventory.Description, tblPartsInventory.Retail,
tblPartsInventory.Cost, tblLocation.Location
ORDER BY tblPartsInventory.Line, tblPartsInventory.PartNumber
PIVOT tblLocation.CountDate;

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.google.c om...
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"

Nov 13 '05 #3

P: n/a
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.1652$_5.1137@clgrps13>...
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")
tblPartsInventory -- which store info on the part itself, and
tblLocation -- which stores information on bin locations and quantities.

tblPartsInventory
- 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 tblPartsInventory.Line, tblPartsInventory.PartNumber,
tblPartsInventory.Description, tblPartsInventory.Retail,
tblPartsInventory.Cost, tblLocation.Location
FROM tblPartsInventory INNER JOIN tblLocation ON tblPartsInventory.PartID =
tblLocation.PartID
GROUP BY tblPartsInventory.Line, tblPartsInventory.PartNumber,
tblPartsInventory.Description, tblPartsInventory.Retail,
tblPartsInventory.Cost, tblLocation.Location
ORDER BY tblPartsInventory.Line, tblPartsInventory.PartNumber
PIVOT tblLocation.CountDate;

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.google.c om...
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"

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.