• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

Is there something like This

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
illion
Asked:
illion
  • 8
  • 5
  • 2
  • +3
1 Solution
 
PhilAICommented:
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
 
leavinmateCommented:
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
 
LunaSkyeCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
MoonCalfCommented:
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
 
MoonCalfCommented:
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
 
illionAuthor Commented:
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
 
PhilAICommented:
SELECT ((fldAdults * fldHandlingFee) + (fldChilds * fldHandlingFee)) + TransferCost As Total
FROM TourOperator
INNER JOIN Party
ON TourOperator.ID = Party.ON
0
 
PhilAICommented:
Change the names I have used: fldAudlts, fldHandlingFee, etc for your exact field names obviously!

Good luck, Phil
0
 
illionAuthor Commented:
What is Party.ON ?
Could you please explain a little bit more how i should set this up?
Is this a lookup query?
0
 
illionAuthor Commented:
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
 
PhilAICommented:
I meant to say, Party.ID, not ON

That was a TYPO
0
 
illionAuthor Commented:
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
 
PhilAICommented:
I think the problem is in your SQL tables... Try aasking in the SQL or Access forums?
0
 
illionAuthor Commented:
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
 
Z_BeeblebroxCommented:
Is this what you want?

Select *, Adults+Childs AS Total FROM Party

Zaphod.
0
 
illionAuthor Commented:
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
 
LunaSkyeCommented:
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
 
illionAuthor Commented:
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
 
illionAuthor Commented:
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
 
Z_BeeblebroxCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 8
  • 5
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now