Solved

Is there something like This

Posted on 2004-04-26
20
167 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 75 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now