How to sum specific records? 1

[ozean]

I have made a invoiceform (main form) with a subform where I fill in articles bought by customer. Each article contains of Item#,Article,Quantity, description and price. Now I want to sum the total of all lineitems (articles). I use the Sum() function in the subform and a reference in the mainform to the textbox in the subform with success. Now to my question, I want to sum all articles in invoice where the article is named "floppydisk" in a textbox in mainform. I have tried to use DSum()with criteria "floppydisk" in subform (looks fine in subform with correct price, with price for all "floppydisk"-lineitems which is correct). When I then reference a textbox in mainform to the textbox in subform the sum of the lineitems where I have floppydisk is not correct.

This is how it looks like in subform:
=DSum([Price];"[tbllineitems]";"Article=floppydisk")

Where tbllineitems is the table with all articles picked at mainform.

How can I sum the lineitems correctly in mainform.

BR
Stefan from Sweden

 

[GingerR]

I'm assuming you want to only sum the "floppydisk" articles for the invoice on the main form?

re: your dsum statement, I think you are not referencing the Invoice number? Also, not sure about the semicolons or not having the field name in quotes.

You could just put on the main form (not the subform), a text box with this:

=dsum("Price","tblLineItems","Invoice = " & Invoice & " and Article = 'FloppyDisk'")

you have to have single quotes around the word 'FloppyDisk'.
The above is assuming your invoice number is a number.

Another option, kind of going along with what you've already done for your normal summing:

In the subform, how about adding a calculated field in the recordsource, i.e.

FloppySum: iif(Article = "FloppyDisk",Price,null)

This should only show the price if the article is a floppydisk. Then in the subform, make your usual text box with

=sum(FloppySum)

then reference that on your main form.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya ozean . . . . .

If you want the total Article = floppydisk for the current invoice . . . . try this:

[blue]=DSum("[Quantity]*[Price]","tblLineItems","([Article]='floppydisk') AND " & "([[purple][b]InvoicePK[/b][/purple]]=" & Forms![purple][b]InvoiceFormName[/b][/purple]![purple][b]InvoicePK[/b][/purple] & ")")[/blue]

See Ya! . . . . . .

 

[ozean]

Thanks a lot you both for taking your time for helping me. I tried to use the calculated field and it calculates correctly, but I can not get the textbox in the footer to sum the field of some strange reason (#Error). Even the Sum of the price that worked fine before just give me an #Error. I can not figure out what the problem is.

Thanks again!!

 

[TheAceMan1]

ozean . . . . .

Just to make sure were on target!:

[blue]In the footer of your [blue]Invoice/Main form[/blue], you want a calculated control to show you the total cost of all floppydisk Articles (which appear in the subform) for the current Invoice.[/blue]

[blue]Is this correct?[/blue]

Post the [blue]Table Names[/blue], [blue]PrimaryKey Names[/blue], [blue]ForeignKeyNames[/blue], and the [purple]relationship (connecting fields)[/purple] for the two tables. Also post the form/subform names.

Post the [purple]Sum function[/purple] your using on the subform . . .

Post the correct field names (actual) for those you've already given if they are different.

If you used the DSum function I gave, post it as you have it now . . . .

See Ya! . . . . . .

 

[ozean]

Yes this is correct,

Table Name for main form = tblInvoices
Table Name for sub form = tbllineitems

Primary key for main form = InvoiceID
Primary key for sub form = RowID

Relationships - Field InvoiceID in tblInvoices is related "one to many" to field InvoiceID in table tbllineitem.

Main form name = FrmInvoice
Sub form name = Invoice lineitems subform

The sum function I am using is: =Sum([Price])but there is an error right now.

Swedish greetings

 

[ozean]

I changed the function to =Sum([Price]*1) and then I get a correct value in the textbox. But when I close and open the subform again it´s an #Error again. When I add *1 again its still an error, but if I change to a new number, i.e *2 I get a correct value multiplied with 2. Very strange?! Any ideas ?

 

[GingerR]

is there a price in every row? Are any blank/null?
If you write a TOTALS query on tblLineItems, summing the PRICE field, is there ERROR?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

ozean . . . . .

[blue]The sum function I am using is: [purple]=Sum([Price])[/purple][/blue]

The [blue]Sum function[/blue] has [purple]no form reference to price[/purple], as such [blue]it has to be on a form containing the field [Price].[/blue] I believe this was [blue]origionally your subform[/blue] . . . Put it back and lets see if we can resolve your origional problem.

Now, whats confusing is [blue]you have Quantity & Price fields[/blue], [purple]yet your only summing price![/purple] Is price a calculated control?

Even if price is calculated, I'm still at a loss, as [purple]Quantity[/purple] times a [purple]Base Price[/purple] has yet to reveral itself . . . .

Perhaps you can explain! . . . .

See Ya! . . . . . .

 

[ozean]

Hi GingerR!

Yes, there is a price in every row and no one is blank/null. I made a query with the price column and sum function, and I got a correct figure.

 

[ozean]

Hi AceMan !

I am sorry to confuse you but I tried to ask the question by translating my problem from swedish to english. To be able to explain my problem I just wrote the lineitems as articles and explained the db as a invoice db. Actually my database is a db where I put in all my bills.

Each month has its own bills. Thats the same as an invoice that has its own articles. There is no quantity field in tbllineitems. It is only a price field. This is how it looks like.

|Reciever|Type |Explanation |Price|Date |
|Telia |Stefan | Telephonebill |297 |2005-01-09|
|ICA |Camilla|Foodcompany |3000 |2005-01-09|

In the main form I want to see three textboxes at the bottom with the total of Stefans, Camillas and all bills.

 

[TheAceMan1]

ozean . . . . .

[blue]Wow! . . . . what a new world it is! . . . . [/blue] ([purple]You do have quantity in your post origination!)[/purple] . . . .

In any case, for individual [purple]Type[/purple] total use the following:

[blue]=DSum("[Price]","tblLineItems","([Type]='[purple][b]Stefan[/b][/purple]') AND " & "([InvoiceID]=" & Forms!FrmInvoice!InvoiceID & ")")[/blue]

For total [purple]Invoice[/purple] use the following:

[blue]=DSum("[Price]","tblLineItems","[InvoiceID]=" & Forms!FrmInvoice!InvoiceID)[/blue]

See Ya! . . . . . .

 

[ozean]

TheAceMan1...

Are the codes to be pasted in mainform? I will try it when I get back from work.

Thanks a lot!

 

[TheAceMan1]

ozean . . . . .

Yes! . . . . . I believe you have then in the footer . . .

See Ya! . . . . . .

 

[ozean]

YES! It worked!!!! First it didn´t but when I changed from "," to ";" (as described in expression builder)it worked.

Thanks again!!! :-D:-D:-D