?
Solved

Is there something like This

Posted on 2004-04-26
20
Medium Priority
?
171 Views
Last Modified: 2010-05-02
im calling a query from a table field, the query takes two values and adds them together. Party.Adults + Party.Childs
im calling the query from a table called Invoices, in a field called Total, Invoices.Total

Here the problem, i need to get the value of Invoices.Party in the query, so this statement can be fulfilled,

"SELECT Adults, Childs FROM Party WHERE Party = " & Invoices.Party

can you use something like this.Party, the query must know which party' adults and childs its supposed to be adding.

Thanks
Robert
0
Comment
Question by:illion
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +3
20 Comments
 
LVL 6

Expert Comment

by:PhilAI
ID: 10919171
I'm not quite following exactly what you have and want, but I believe you need to arrange your tables so that they are using the INNER JOIN operation.

Thing is, you are asking a SQL question, not a VB question.
0
 
LVL 1

Expert Comment

by:leavinmate
ID: 10919256
Okay...if I understand the question, you have 1 table with 2 columns in it that you want to add

1 for adults total
1 for child total

SELECT Party.Adults + Party.Childs As Party_Totals
FROM Party WHERE Party = " & Invoices.Party

0
 
LVL 6

Expert Comment

by:LunaSkye
ID: 10919303
Here is my take:

You are looking for a TOTAL COUNT, that is Children + Adults.

So you would have to create a virtual field for use in that query that adds the two value together.

"SELECT Adults, Childs, (Adults + Childs) AS MyTotal FROM Party WHERE Party = " & Invoices.Party

This Part:
(Adults + Childs) AS MyTotal

will create the virtual field called "MyTotal"  in the results of your query.. This will contain the total of childs + adults.

-andrew
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:MoonCalf
ID: 10919342
Hi.

I can't for the life of me see why you want this, but this is what I read the question to mean.....

"SELECT Adults, Childs, Party FROM Party WHERE Party = " & Invoices.Party


Ta,

MoonCalf.
0
 
LVL 1

Expert Comment

by:MoonCalf
ID: 10919370
I take it back.   I think you need the following....


select adults, childs from party where party=(select party from invoices)


or something to that effect.

MoonCalf.
0
 

Author Comment

by:illion
ID: 10919827
Ok, i have a table Tour Operator:

ID | Tour Operator | Handling Fee | Handling Child | Transfer Fee
1     CaribWorld             45,00               0,75                 50,00

and then i have another table Party:

ID | Party Name | Adults | Childs | Handling Cost | Transfer Cost | Total Cost
1     Smiths, K        2           1               x                      50,00              y

i want to have x,y:

x = Adults * [Handling Fee] + Childs * [Handling Child] * [Handling Fee]
y = x + [Transfer Cost]     'in this case 50,00

im working in access so i guess i have to do this with a query of some kind...
0
 
LVL 6

Expert Comment

by:PhilAI
ID: 10920356
SELECT ((fldAdults * fldHandlingFee) + (fldChilds * fldHandlingFee)) + TransferCost As Total
FROM TourOperator
INNER JOIN Party
ON TourOperator.ID = Party.ON
0
 
LVL 6

Expert Comment

by:PhilAI
ID: 10920370
Change the names I have used: fldAudlts, fldHandlingFee, etc for your exact field names obviously!

Good luck, Phil
0
 

Author Comment

by:illion
ID: 10920676
What is Party.ON ?
Could you please explain a little bit more how i should set this up?
Is this a lookup query?
0
 

Author Comment

by:illion
ID: 10920787
Ok, maybe its easier if i add the field Tour Operator in the Party table so they have a relation:

table Tour Operator:

ID | Tour Operator | Handling Fee | Handling Child | Transfer Fee
1     CaribWorld             45,00               0,75                 50,00

table Party:

ID | Party Name | Tour Operator | Adults | Childs | Handling Cost | Transfer Cost | Total Cost
1     Smiths, K        CaribWorld         2           1               x                      50,00              y

now you have to get the information of the Tour Operator the Party are related to and then you take that Tour Operator' values and calculate x and y.
so x and y can only be one thing, if this would have been excel it would have been very easy, then you just tell the Handling column for exampel
to "=SUM(Adults*...)" and youre done, then that column is assigned its right value.
0
 
LVL 6

Expert Comment

by:PhilAI
ID: 10920822
I meant to say, Party.ID, not ON

That was a TYPO
0
 

Author Comment

by:illion
ID: 10920889
When i use your SQL statement i get a list of the right values, but its not supposed to be a list, cause it can actually only be one value, cause its only one party' adults and chils and tour operator, if you understand what i mean..
0
 
LVL 6

Expert Comment

by:PhilAI
ID: 10921002
I think the problem is in your SQL tables... Try aasking in the SQL or Access forums?
0
 

Author Comment

by:illion
ID: 10921539
Hmm, ok let put it this way, you only have on table "Party":

ID | Party Name | Adults | Childs | Total
1     Smiths            2            1         3
2     Kruse              3            2         5

you see i only want the Total to add adults and childs, its a simpel addition.
How is this possible in Access?
0
 
LVL 7

Expert Comment

by:Z_Beeblebrox
ID: 10921802
Is this what you want?

Select *, Adults+Childs AS Total FROM Party

Zaphod.
0
 

Author Comment

by:illion
ID: 10921916
Where should i put that statement? Should i make a query?
I tried to make a query but it said something about you cant use the table you want to use for lookup query...
0
 
LVL 6

Expert Comment

by:LunaSkye
ID: 10928221
Im sorry ilion,
I think i speak for all here, i am still confused as to what you want.

Do you want the total cost of a tour?  (Adults * Adult fee) + (children * Child fee) + (Transfer Fee) + (Handling Fee) ?

Is it your goal to see the total cost of all this in a single field of a query?

-andrew
0
 

Author Comment

by:illion
ID: 10928953
Lets say you just have created your first table, the name of the table is "Party":

ID | Party Name | Adults | Childs | Total
1     Smiths            2            1         x
2     Kruse              3            2         y

it looks like this, now i want the Total to be a function of Adults and Childs,
what i mean is when i type or change the value in Adults like for instance
i change the party Kruse's adults 3 to a 4 then i want the Total for that row to change automatically.
so then y = 4 + 2 = 6
if i change smiths value of 2 adults to 3 adults i want the total for that party to change
x = 3 + 1 = 4

Hope this makes it a little bit more clear
0
 

Author Comment

by:illion
ID: 10928983
In excel it would have been like this:

  |    A     |      B      |      C      |       D      |           E             |
1      1         Smiths         2               1          =SUM(C1+D1)
2      2         Kruse           3               2          =SUM(C2+D2)
0
 
LVL 7

Accepted Solution

by:
Z_Beeblebrox earned 300 total points
ID: 10933785
You should probably ask this question in the Access area. What you are looking for is a computed or calculated field. I'm not sure if Access supports these, but the experts in the Access area would know.

http://www.experts-exchange.com/Databases/MS_Access/

Zaphod.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question