Programming Help

Web Development



What's Here?

  • Members: 150,428
  • Replies: 508,502
  • Topics: 80,162
  • Snippets: 2,669
  • Tutorials: 709
  • Total Online: 1,079
  • Members: 90
  • Guests: 989

Welcome to Dream.In.Code
Become an Expert!

Join 150,428 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,079 people online right now. Registration is fast and FREE... Join Now!




a SQL lookup

 
 Reply to this topicStart new topic

a SQL lookup
raeNet
14 Apr, 2008 - 09:46 AM
Post #1

D.I.C Head
**

Joined: 1 Nov, 2007
Posts: 115


My Contributions
Using the MS Access Northwind dbase, I am to use the Orders table to write the SQL query that returns the sum of shipping fees grouped by customer.

Here's my SQL statement:
CODE

SELECT [Customer ID], SUM([Shipping Fee]) AS [Sub Total by Customer]
FROM Orders
GROUP BY [Customer ID];


Can anyone help me to display the output column of Customer ID as Company? In Design View, I wrote the following via the Lookup tab on Customer ID:
CODE

SELECT ID, Company FROM Customers ORDER BY Company;


Is this how I accomplish a SQL lookup?

Any help with this is greatly appreciated! Thanks in advance.

This post has been edited by raeNet: 14 Apr, 2008 - 09:48 AM
User is offlineProfile CardPM
+Quote Post

PsychoCoder
RE: A SQL Lookup
14 Apr, 2008 - 09:59 AM
Post #2

using DIC.Core;
Group Icon

Joined: 26 Jul, 2007
Posts: 9,483



Thanked: 162 times
Dream Kudos: 9075
Expert In: VB, VB.Net, C#, SQL, ASP, ASP.Net, Web Development, HTML, CSS, Win32 API, Javascript, mySQL, J#, Boo.Net

My Contributions
With SQL (And Access) you can add an alias to your column with the AS keyword, like


sql

SELECT [Customer ID] As 'Company', SUM([Shipping Fee]) AS ['Sub Total by Customer']
FROM Orders
GROUP BY [Customer ID];



I hope that's what you were asking?

Also, when you have a column alias that has spaces in it (as yours does) you need single quotes around it. I fixed your example to reflect this
User is offlineProfile CardPM
+Quote Post

P4L
RE: A SQL Lookup
14 Apr, 2008 - 10:02 AM
Post #3

Geek 4 Life
Group Icon

Joined: 7 Feb, 2008
Posts: 1,268



Thanked: 6 times
Dream Kudos: 125
My Contributions
In design view, type in "Company:" into the field with the CustomerId, but in looking at the database itself, it already shows the header as Customer. SQL code
CODE
Select CustomerId as Company FROM Customers ORDER BY Company;
User is online!Profile CardPM
+Quote Post

raeNet
RE: A SQL Lookup
14 Apr, 2008 - 10:10 AM
Post #4

D.I.C Head
**

Joined: 1 Nov, 2007
Posts: 115


My Contributions
The output that I get, even with the code you suggested, gives me:

Customer ID Sub Total by Customer
1---------------$ 0.00
3---------------$ 14.00
4---------------$ 18.00
6---------------$624.00


I need the Customer ID numbers in the first column to reflect the customer name which is in the Company field.

This post has been edited by raeNet: 14 Apr, 2008 - 10:11 AM
User is offlineProfile CardPM
+Quote Post

P4L
RE: A SQL Lookup
14 Apr, 2008 - 10:20 AM
Post #5

Geek 4 Life
Group Icon

Joined: 7 Feb, 2008
Posts: 1,268



Thanked: 6 times
Dream Kudos: 125
My Contributions
Try this
CODE
SELECT Customers.CompanyName AS Expr1,
Sum(Orders.Freight) AS SumOfFreight

FROM Customers, Orders

WHERE ((([Orders].[CustomerId])=[Customers].[CustomerId]))
GROUP BY Customers.CompanyName;


This is the query that I created in Northwind db.

This post has been edited by programming4life: 14 Apr, 2008 - 10:21 AM
User is online!Profile CardPM
+Quote Post

raeNet
RE: A SQL Lookup
14 Apr, 2008 - 10:33 AM
Post #6

D.I.C Head
**

Joined: 1 Nov, 2007
Posts: 115


My Contributions
I'm sorry but that query gives me Enter Parameter Value popups galour.
User is offlineProfile CardPM
+Quote Post

P4L
RE: A SQL Lookup
14 Apr, 2008 - 10:44 AM
Post #7

Geek 4 Life
Group Icon

Joined: 7 Feb, 2008
Posts: 1,268



Thanked: 6 times
Dream Kudos: 125
My Contributions
I find that odd speaking that I created the query in Northwind. Here are the screen shots showing that it does work.

What version of Access are you using?


Attached File(s)
Attached File  Northwind.doc ( 101k ) Number of downloads: 22
User is online!Profile CardPM
+Quote Post

raeNet
RE: A SQL Lookup
14 Apr, 2008 - 11:01 AM
Post #8

D.I.C Head
**

Joined: 1 Nov, 2007
Posts: 115


My Contributions
QUOTE(raeNet @ 14 Apr, 2008 - 11:33 AM) *

I'm sorry but that query gives me Enter Parameter Value popups galour.



My Northwind Customer's table doesn't have a CustomerName, ContactName, etc.

It has the following:

ID
Company
Last Name
First Name
E-mail Address
Job Title
Business Phone
Home Phone, etc.,
User is offlineProfile CardPM
+Quote Post

P4L
RE: A SQL Lookup
14 Apr, 2008 - 11:05 AM
Post #9

Geek 4 Life
Group Icon

Joined: 7 Feb, 2008
Posts: 1,268



Thanked: 6 times
Dream Kudos: 125
My Contributions
For your tables, it should look like this...

CODE
SELECT Customers.Company,
Sum(Orders.Freight) AS SumOfFreight

FROM Customers, Orders

WHERE ((([Orders].[CustomerId])=[Customers].[Id]))
GROUP BY Customers.Company;


The only other field that I am scetchy on is the fields for the Orders table, but just play with the fields that are in your NorthWind db for each of the tables that are needed until it works. This is the best that I can do since we do not have the same db.

This post has been edited by programming4life: 14 Apr, 2008 - 11:06 AM
User is online!Profile CardPM
+Quote Post

raeNet
RE: A SQL Lookup
14 Apr, 2008 - 11:12 AM
Post #10

D.I.C Head
**

Joined: 1 Nov, 2007
Posts: 115


My Contributions
Thank you for all your help.

I got it to work! In Orders table (Design View) I removed the code referenced above in the Lookup tab. Evidently, it was blocking it?? I don't know, this is the first I've worked with SQL.

CODE

SELECT ID, Company FROM Customers ORDER BY Company;


Again, thanks for your help with this.
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic
Time is now: 1/9/09 08:47PM
Invision Power Board v2.1.7 © 2009  IPS, Inc.
Licensed to: MediaGroup1

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter

Live Help!

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month