• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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