Solved

Is there something like This

Posted on 2004-04-26
20
166 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 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

13 Experts available now in Live!

Get 1:1 Help Now