How to use SUM as a value

Hi,
to display this sum you can add calculated field to your SQL query in the PREPAID table.
If you encounter a difficulty with query composition please post here your database type and both tables structure. I'll help you.

I did not figure out the Calculate syntax. But, I did modify the SQL so that the Sum of hours shows up. But, now the "Edit" "View" "Copy" and link to the sub-table "apply" do not work.
I am using MS Access. The two tables and their fields are:
Table: prepaid

Fields: ID, company, date, prepaidhours, notes, status
Table: apply

Fields: ID, ID_prepaid, date, hours, notes
Here is the SQL I entered in ASPRunner for the 'prepaid' SQL:

SELECT DISTINCTROW prepaid.ID, prepaid.company, prepaid.date, prepaid.prepaidhours, prepaid.status, prepaid.notes, Sum(apply.hours) AS [Sum Of hours]

FROM prepaid INNER JOIN apply ON prepaid.ID = apply.ID_prepaid

GROUP BY prepaid.ID, prepaid.company, prepaid.date, prepaid.prepaidhours, prepaid.status, prepaid.notes
It shows the information I want. That is good. But, it does not allow clicking to the 'apply' table to which 'prepaid' is the master.
I think there is a different way to do this (without DISTINCTROW) that should work.
Thanks in advance for any suggestions.

<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=5797&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />

I'm not sure what exactly you plan to Edit here.
Let's assume you have a several records in this table.
ID Company PrepaidHours

-------------------------------------

1 Apple 12

2 Microsoft 7

1 Apple 10

2 Microsoft 23

2 Microsoft 5
Your SQL query returns something like this:
ID Company PrepaidHours

-------------------------------------

1 Apple 22

2 Microsoft 35
These records are aggregated. There is nothing to Edit here.
I guess you have to build two separate set of pages, first one for edit/view/add capabilities and second one to display aggregated data.
I hope this helps a bit.

I am not explaining this very well. :blink Sorry. I will try again.
The purpose of the two table is to sell a product, and to track the use of the product. The product is prepaid hours of consulting work. The tracking is when some of the hours are used.
The first table has the customer info and the number of prepaid consulting hours they bought. The second table is a record of when the prepaid consulting hours were used.
So, the first table has one record for each purchase of prepaid hours. The second table has several records, each showing the date that some prepaid hours were used.
The Query copied previously shows the information I want on the prepaid_list.asp. It has the company info, the number of prepaid hours purchased, and a sum of prepaid hours used (from the apply table). Kind of like this:

ID Company PrepaidHours SumOfHours

-------------------------------------

1 Apple 12 5

2 Microsoft 7 7
The 'apply' table will have something like this:

ID ID_prepaid Hours

-------------------------------------

1 1 3

2 1 1

3 2 6

4 2 1

5 1 1
The query I used does show the things from the first example. This is based on the 'prepaid' table (Master) and the 'apply' table (Client). The prepaid_list.asp appears to be fine, except that the links (edit, view, apply) do not work.
When I try the 'apply' link, which should go to the apply_list.asp page, I get this error:

Error number -2147217900

Error description [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'DISTINCTROW prepaid.ID'.
When I try the 'edit' or view' link I get a "page cannot be displayed" error with

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'prepaid.notes where ([ID]=4)'.

/prepaid_edit.asp, line 439
Somewhere in my mind I know what I am trying to do, but sometimes the thoughts get confused when typing. I hope this message helps explain a bit better what I am trying to accomplish.
Thank you very much for putting up with me.

I understand your tables structure now however it's still not clear what you need to edit.
In my understanding you need three tables:

tblCompany - company info

tblPrepaid - prepaid hours info, linked by company id

tblUsed - used hours info, linked by company id
Company page will be the main page of application. tblPrepaid and tblUsed will be Detail tables. If SQL query with GROUP BY doesn't work in ASPRunner you can create a new query in MS Access on the top of this SQL and use this query as a datasource in ASPRunner.

I need to be able to edit the 'prepaid' and the 'apply' tables. The link between them is the 'prepaid.ID', not the company ID. I need to track the single purchase of prepaid hours, and also track the multiple instances that these hours were used.
I am trying to show the SUM of the hours used in the 'apply' table when I look at the 'prepaid' table. This will show me every company with prepaid hours, and how many of the prepaid hours have been used.
It seems that I should be able to make a calculated field in the 'prepaid' configuration to show the SUM of the hours in the 'apply' table assigned to each prepaid ID.
Example:

prepaid_list.asp?

ID company date prepaidhours notes status "SUM of hours applied"

11 Qwest 9/6/2005 55.00 blah...blah Open 16
apply_list.asp?masterTargetPageNumber=&masterkey=11

ID ID_prepaid hours notes

62 11 5.50 blah...blahhhhh

81 11 10.50 more notes/......

Total: 16.00
If I could get it to work this way, I would be able to look at one page and see any client that has purchased prepaid hours and how many they have used.
Perhaps this is not possible, but it would be nice.
Thanks for you continued help.
John

You can achieve what you looking for if you use three tables, using tblCompany as a master while tblApply and tblPrepaid are details. Connect them using CompanyID which is more logical. Display master table info on details pages to see amount of prepaid and used hours.

First - Thank you for your input. Your support has been fantastic.
I think I may not be explaining myself very well. But, with your help, a solution has been found.
Connecting the 'prepaid' and 'apply' tables to the 'customers' table won't work because a single customer can have several instances of prepaid hours. And, I still don't see how the SUM of the used hours would show up on any other page than the 'apply' page.
But, I think I found a solution! <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=5866&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />

In MS Access I created a Query that that selects all the items from the 'prepaid' and the 'apply' tables and gives the SUM of the prepaid hours.

I used this Query in ASPRunner to generate a page. In the 'Formatting' tab, I changed the 'view' to 'hyperlink' for the ID. The link is: apply_list.asp?masterTargetPageNumber=&masterkey=
I also added to the query select this: [ID] AS Edit

I changed the 'view' of 'edit' to 'hyperlink'. The link is:

prepaid_edit.asp?editid2=&editid3=&TargetPageNumber=1&todo=view&masterkey=&editid=
The tricky part of that link is that the Value of the field is put onto the end of the link. In the first hyperlink it adds the ID to the end of the link which makes it the MasterKey. For the second link, I had to change the order of &editid= so that it would be at the end of the link.
The result is that I can now open one page REPORT_Prepaid_list.asp and see all companies that have prepaid hours. From that page I can jump to the 'apply' page to apply more hours that were used, and I can jump to the 'prepaid_edit' page to change the status.
Here is the Query "REPORT_Prepaid" in MS Access I used:

SELECT DISTINCTROW [prepaid].[ID], [prepaid].[company], [prepaid].[date], [prepaid].[prepaidhours], [prepaid].[status], [prepaid].[ID] AS Edit, First([prepaid].[notes]) AS [First Of prepaid_notes], First([apply].[ID]) AS [First Of apply_ID], First([apply].[ID_prepaid]) AS [First Of ID_prepaid], First([apply].[item]) AS [First Of item], Sum([apply].[hours]) AS [Sum Of hours], First([apply].[notes]) AS [First Of apply_notes]

FROM prepaid LEFT JOIN apply ON [prepaid].[ID]=[apply].[ID_prepaid]

WHERE ((([prepaid].[status])<>'Closed'))

GROUP BY [prepaid].[ID], [prepaid].[company], [prepaid].[date], [prepaid].[prepaidhours], [prepaid].[status];