A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Reload this Page Problem with query results
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Problem with query results


« Previous Thread | Next Thread »

 
 
Thread Tools Display Modes
  #1  
Old January 16th, 2006, 04:57 PM posted to microsoft.public.access.queries
Jim
external usenet poster
  
Posts: n/a
Default Problem with query results
Hello,
I have an Access 2003 query that changes descriptions for a bill of lading
based on the hazardous content. For example "0 " is not, "1" is hazardous
and "2" is hazardous, but with a different description. The problem I'm
having is that if I have two different items on the bill that are hazardous;
instead of writing the description for each, it groups them together then
adds them. What I need is:

Product One "1" hazardous description
Product Two "2" hazardous description

What I get is:

Product One "3" no description
Product Two "3" no description

Thanks for any help.

Jim
  #2  
Old January 16th, 2006, 05:45 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
  
Posts: n/a
Default Problem with query results
Please post the SQL of the query.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Jim" wrote:

Hello,
I have an Access 2003 query that changes descriptions for a bill of lading
based on the hazardous content. For example "0 " is not, "1" is hazardous
and "2" is hazardous, but with a different description. The problem I'm
having is that if I have two different items on the bill that are hazardous;
instead of writing the description for each, it groups them together then
adds them. What I need is:

Product One "1" hazardous description
Product Two "2" hazardous description

What I get is:

Product One "3" no description
Product Two "3" no description

Thanks for any help.

Jim
  #3  
Old January 16th, 2006, 05:51 PM posted to microsoft.public.access.queries
OfficeDev18 via AccessMonster.com
external usenet poster
  
Posts: n/a
Default Problem with query results
Interesting problem, but please copy and paste the SQL.

Thanks,
Sam

Jim wrote:
Hello,
I have an Access 2003 query that changes descriptions for a bill of lading
based on the hazardous content. For example "0 " is not, "1" is hazardous
and "2" is hazardous, but with a different description. The problem I'm
having is that if I have two different items on the bill that are hazardous;
instead of writing the description for each, it groups them together then
adds them. What I need is:

Product One "1" hazardous description
Product Two "2" hazardous description

What I get is:

Product One "3" no description
Product Two "3" no description

Thanks for any help.

Jim

--
Sam

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200601/1
  #4  
Old January 16th, 2006, 08:22 PM posted to microsoft.public.access.queries
Jim
external usenet poster
  
Posts: n/a
Default Problem with query results
Here you go.

Hazard: Sum(IIf([tblInvoiceDetail.strProductID] Like "14790L" Or
[tblInvoiceDetail.strProductID] Like
"*sbg*",1,IIf([tblInvoiceDetail.strProductID] Like "5gwb" Or
[tblInvoiceDetail.strProductID] Like "gpc",2,0)))

"OfficeDev18 via AccessMonster.com" u14095@uwe wrote in message
news:5a73fbba17419@uwe...
Interesting problem, but please copy and paste the SQL.

Thanks,
Sam

Jim wrote:
Hello,
I have an Access 2003 query that changes descriptions for a bill of lading
based on the hazardous content. For example "0 " is not, "1" is hazardous
and "2" is hazardous, but with a different description. The problem I'm
having is that if I have two different items on the bill that are
hazardous;
instead of writing the description for each, it groups them together then
adds them. What I need is:

Product One "1" hazardous description
Product Two "2" hazardous description

What I get is:

Product One "3" no description
Product Two "3" no description

Thanks for any help.

Jim

--
Sam

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200601/1
  #5  
Old January 16th, 2006, 11:56 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
  
Posts: n/a
Default Problem with query results
What you posted can't add up to 3 therefore be your problem. You need to post
the entire SQL statement. If the SQL statement is based on another query, we
need that too. I'm betting that you have a totals query summing up the 1s and
2s.

A nice touch would be to include the names of the Primary Keys on any tables
used in the query.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Jim" wrote:

Here you go.

Hazard: Sum(IIf([tblInvoiceDetail.strProductID] Like "14790L" Or
[tblInvoiceDetail.strProductID] Like
"*sbg*",1,IIf([tblInvoiceDetail.strProductID] Like "5gwb" Or
[tblInvoiceDetail.strProductID] Like "gpc",2,0)))

"OfficeDev18 via AccessMonster.com" u14095@uwe wrote in message
news:5a73fbba17419@uwe...
Interesting problem, but please copy and paste the SQL.

Thanks,
Sam

Jim wrote:
Hello,
I have an Access 2003 query that changes descriptions for a bill of lading
based on the hazardous content. For example "0 " is not, "1" is hazardous
and "2" is hazardous, but with a different description. The problem I'm
having is that if I have two different items on the bill that are
hazardous;
instead of writing the description for each, it groups them together then
adds them. What I need is:

Product One "1" hazardous description
Product Two "2" hazardous description

What I get is:

Product One "3" no description
Product Two "3" no description

Thanks for any help.

Jim

--
Sam

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200601/1
  #6  
Old January 17th, 2006, 04:25 PM posted to microsoft.public.access.queries
Jim
external usenet poster
  
Posts: n/a
Default Problem with query results
The primary keys a

tblInvoice - strOrderNumber
tblInvoiceDetail - strOrderNumber
tblCustomerShipTo - strShipID & strCustomerID
tblWarehouse - strWarehouseID

Here is the SQL from the query.

SELECT tblInvoice.strOrderNumber, tblInvoice.strCustomerID,
tblInvoice.strShipToCompany, tblInvoice.strShipToAddress,
[tblInvoice.strShipToCity] & " " & [tblInvoice.strShipToState] & " " &
[tblInvoice.strShipToZip] AS CityAdd, tblInvoice.strShipVia,
Sum(IIf(([tblInvoiceDetail.strProductID]) Like
"14790L",[dblQtyShipped]/12,IIf([tblInvoiceDetail.strProductID] Like
"GTS","1"*"1",[dblQtyShipped]))) AS PkgCt, tblInvoice.dtmDateShipped,
tblInvoice.strTerms, FormatCurrency(IIf(([tblInvoice.strTerms]) Like
"*c.o.d.",[curOrderTotal],0)) AS OrderTotal,
IIf(([tblInvoice.strShipToPhoneNumber]) Is
Null,[strBillToPhoneNumber],[tblInvoice.strShipToPhoneNumber]) AS Phone,
Sum(IIf([tblInvoiceDetail.strProductID] Like "14790L" Or
[tblInvoiceDetail.strProductID] Like
"*sbg*",1,IIf([tblInvoiceDetail.strProductID] Like "5gwb" Or
[tblInvoiceDetail.strProductID] Like "gpc",2,0))) AS Hazard,
tblInvoice.strCustomerID, IIf(([tblCustomerShipTo.Notes]) Is
Null,"",([tblCustomerShipTo.Notes])) AS [Ship Notes], IIf([strBillToCountry]
Is Null,"USA",([strBillToCountry])) AS Country, tblInvoice.strCustomerPO AS
Purchase, tblWarehouse.strWarehouseID, tblWarehouse.strAddress AS WareAdd,
[tblWarehouse.strCity] & " " & [tblWarehouse.strState] & " " &
[tblWarehouse.strZip] AS WareCityAdd
FROM (tblInvoice LEFT JOIN tblCustomerShipTo ON (tblInvoice.strCustomerID =
tblCustomerShipTo.strCustomerID) AND (tblInvoice.strShipID =
tblCustomerShipTo.strShipID)) LEFT JOIN (tblWarehouse RIGHT JOIN
tblInvoiceDetail ON tblWarehouse.strWarehouseID =
tblInvoiceDetail.strWarehouseID) ON tblInvoice.strOrderNumber =
tblInvoiceDetail.strOrderNumber
GROUP BY tblInvoice.strOrderNumber, tblInvoice.strCustomerID,
tblInvoice.strShipToCompany, tblInvoice.strShipToAddress,
[tblInvoice.strShipToCity] & " " & [tblInvoice.strShipToState] & " " &
[tblInvoice.strShipToZip], tblInvoice.strShipVia, tblInvoice.dtmDateShipped,
tblInvoice.strTerms, FormatCurrency(IIf(([tblInvoice.strTerms]) Like
"*c.o.d.",[curOrderTotal],0)), IIf(([tblInvoice.strShipToPhoneNumber]) Is
Null,[strBillToPhoneNumber],[tblInvoice.strShipToPhoneNumber]),
tblInvoice.strCustomerID, IIf(([tblCustomerShipTo.Notes]) Is
Null,"",([tblCustomerShipTo.Notes])), IIf([strBillToCountry] Is
Null,"USA",([strBillToCountry])), tblInvoice.strCustomerPO,
tblWarehouse.strWarehouseID, tblWarehouse.strAddress, [tblWarehouse.strCity]
& " " & [tblWarehouse.strState] & " " & [tblWarehouse.strZip]
HAVING (((tblInvoice.strOrderNumber) Like "*" & [Enter Invoice Number]) AND
((tblInvoice.strShipVia) Not In ("UPS Ground","Fed Ex Ground","UPS
Collect","UPS 2ND DAY","Fed Ex 2Nd Day","UPS Standard","UPS Next Day","UPS 3
Day","U.S. Mail","Federal Express","FED EX OVERNIGHT","Fed Ex 3Rd Day","FED
EX NEXT DAY","Parcel Post","Priority Mail")))
ORDER BY tblInvoice.dtmDateShipped DESC;

Thanks
Jim

"Jerry Whittle" wrote in message
...
What you posted can't add up to 3 therefore be your problem. You need to
post
the entire SQL statement. If the SQL statement is based on another query,
we
need that too. I'm betting that you have a totals query summing up the 1s
and
2s.

A nice touch would be to include the names of the Primary Keys on any
tables
used in the query.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Jim" wrote:

Here you go.

Hazard: Sum(IIf([tblInvoiceDetail.strProductID] Like "14790L" Or
[tblInvoiceDetail.strProductID] Like
"*sbg*",1,IIf([tblInvoiceDetail.strProductID] Like "5gwb" Or
[tblInvoiceDetail.strProductID] Like "gpc",2,0)))

"OfficeDev18 via AccessMonster.com" u14095@uwe wrote in message
news:5a73fbba17419@uwe...
Interesting problem, but please copy and paste the SQL.

Thanks,
Sam

Jim wrote:
Hello,
I have an Access 2003 query that changes descriptions for a bill of
lading
based on the hazardous content. For example "0 " is not, "1" is
hazardous
and "2" is hazardous, but with a different description. The problem I'm
having is that if I have two different items on the bill that are
hazardous;
instead of writing the description for each, it groups them together
then
adds them. What I need is:

Product One "1" hazardous description
Product Two "2" hazardous description

What I get is:

Product One "3" no description
Product Two "3" no description

Thanks for any help.

Jim

--
Sam

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200601/1
  #7  
Old January 18th, 2006, 02:23 AM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
  
Posts: n/a
Default Problem with query results
Try removing the Sum in front of :
Sum(IIf([tblInvoiceDetail.strProductID] Like "14790L" Or
[tblInvoiceDetail.strProductID] Like
"*sbg*",1,IIf([tblInvoiceDetail.strProductID] Like "5gwb" Or
[tblInvoiceDetail.strProductID] Like "gpc",2,0))) AS Hazard,

Also add [tblInvoiceDetail.strProductID] to the Group By clause.

The problem is in there somewhere.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Jim" wrote:

The primary keys a

tblInvoice - strOrderNumber
tblInvoiceDetail - strOrderNumber
tblCustomerShipTo - strShipID & strCustomerID
tblWarehouse - strWarehouseID

Here is the SQL from the query.

SELECT tblInvoice.strOrderNumber, tblInvoice.strCustomerID,
tblInvoice.strShipToCompany, tblInvoice.strShipToAddress,
[tblInvoice.strShipToCity] & " " & [tblInvoice.strShipToState] & " " &
[tblInvoice.strShipToZip] AS CityAdd, tblInvoice.strShipVia,
Sum(IIf(([tblInvoiceDetail.strProductID]) Like
"14790L",[dblQtyShipped]/12,IIf([tblInvoiceDetail.strProductID] Like
"GTS","1"*"1",[dblQtyShipped]))) AS PkgCt, tblInvoice.dtmDateShipped,
tblInvoice.strTerms, FormatCurrency(IIf(([tblInvoice.strTerms]) Like
"*c.o.d.",[curOrderTotal],0)) AS OrderTotal,
IIf(([tblInvoice.strShipToPhoneNumber]) Is
Null,[strBillToPhoneNumber],[tblInvoice.strShipToPhoneNumber]) AS Phone,
Sum(IIf([tblInvoiceDetail.strProductID] Like "14790L" Or
[tblInvoiceDetail.strProductID] Like
"*sbg*",1,IIf([tblInvoiceDetail.strProductID] Like "5gwb" Or
[tblInvoiceDetail.strProductID] Like "gpc",2,0))) AS Hazard,
tblInvoice.strCustomerID, IIf(([tblCustomerShipTo.Notes]) Is
Null,"",([tblCustomerShipTo.Notes])) AS [Ship Notes], IIf([strBillToCountry]
Is Null,"USA",([strBillToCountry])) AS Country, tblInvoice.strCustomerPO AS
Purchase, tblWarehouse.strWarehouseID, tblWarehouse.strAddress AS WareAdd,
[tblWarehouse.strCity] & " " & [tblWarehouse.strState] & " " &
[tblWarehouse.strZip] AS WareCityAdd
FROM (tblInvoice LEFT JOIN tblCustomerShipTo ON (tblInvoice.strCustomerID =
tblCustomerShipTo.strCustomerID) AND (tblInvoice.strShipID =
tblCustomerShipTo.strShipID)) LEFT JOIN (tblWarehouse RIGHT JOIN
tblInvoiceDetail ON tblWarehouse.strWarehouseID =
tblInvoiceDetail.strWarehouseID) ON tblInvoice.strOrderNumber =
tblInvoiceDetail.strOrderNumber
GROUP BY tblInvoice.strOrderNumber, tblInvoice.strCustomerID,
tblInvoice.strShipToCompany, tblInvoice.strShipToAddress,
[tblInvoice.strShipToCity] & " " & [tblInvoice.strShipToState] & " " &
[tblInvoice.strShipToZip], tblInvoice.strShipVia, tblInvoice.dtmDateShipped,
tblInvoice.strTerms, FormatCurrency(IIf(([tblInvoice.strTerms]) Like
"*c.o.d.",[curOrderTotal],0)), IIf(([tblInvoice.strShipToPhoneNumber]) Is
Null,[strBillToPhoneNumber],[tblInvoice.strShipToPhoneNumber]),
tblInvoice.strCustomerID, IIf(([tblCustomerShipTo.Notes]) Is
Null,"",([tblCustomerShipTo.Notes])), IIf([strBillToCountry] Is
Null,"USA",([strBillToCountry])), tblInvoice.strCustomerPO,
tblWarehouse.strWarehouseID, tblWarehouse.strAddress, [tblWarehouse.strCity]
& " " & [tblWarehouse.strState] & " " & [tblWarehouse.strZip]
HAVING (((tblInvoice.strOrderNumber) Like "*" & [Enter Invoice Number]) AND
((tblInvoice.strShipVia) Not In ("UPS Ground","Fed Ex Ground","UPS
Collect","UPS 2ND DAY","Fed Ex 2Nd Day","UPS Standard","UPS Next Day","UPS 3
Day","U.S. Mail","Federal Express","FED EX OVERNIGHT","Fed Ex 3Rd Day","FED
EX NEXT DAY","Parcel Post","Priority Mail")))
ORDER BY tblInvoice.dtmDateShipped DESC;

Thanks
Jim

"Jerry Whittle" wrote in message
...
What you posted can't add up to 3 therefore be your problem. You need to
post
the entire SQL statement. If the SQL statement is based on another query,
we
need that too. I'm betting that you have a totals query summing up the 1s
and
2s.

A nice touch would be to include the names of the Primary Keys on any
tables
used in the query.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Jim" wrote:

Here you go.

Hazard: Sum(IIf([tblInvoiceDetail.strProductID] Like "14790L" Or
[tblInvoiceDetail.strProductID] Like
"*sbg*",1,IIf([tblInvoiceDetail.strProductID] Like "5gwb" Or
[tblInvoiceDetail.strProductID] Like "gpc",2,0)))

"OfficeDev18 via AccessMonster.com" u14095@uwe wrote in message
news:5a73fbba17419@uwe...
Interesting problem, but please copy and paste the SQL.

Thanks,
Sam

Jim wrote:
Hello,
I have an Access 2003 query that changes descriptions for a bill of
lading
based on the hazardous content. For example "0 " is not, "1" is
hazardous
and "2" is hazardous, but with a different description. The problem I'm
having is that if I have two different items on the bill that are
hazardous;
instead of writing the description for each, it groups them together
then
adds them. What I need is:

Product One "1" hazardous description
Product Two "2" hazardous description

What I get is:

Product One "3" no description
Product Two "3" no description

Thanks for any help.

Jim

--
Sam

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200601/1
 



« Previous Thread | Next Thread »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with a query that concatenates field values Amit Running & Setting Up Queries 2 September 30th, 2005 05:39 PM
Toolbars, Drop-Down Menus Rick New Users 1 September 21st, 2005 11:17 AM
Nested in-line Query laura Running & Setting Up Queries 0 February 11th, 2005 12:17 AM
adding 2 fields including null entries Jesse Running & Setting Up Queries 26 January 18th, 2005 05:31 PM
RANKING gambler Running & Setting Up Queries 22 November 4th, 2004 11:53 PM


All times are GMT +1. The time now is 11:56 AM.

Feed Icon - Contact Us - OfficeFrustration excel & word help home - FAQ - Links - Privacy Statement - Top

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.