Taking out £0.00 rows

[colkas]

Hi
I have a form that uses 10 fileds for entry.

The fields are

Building descritption and building price (from 1 to 10).

Not all 10 will be filled in, so in mt report I only want to show the fields that have prices in and I want them all together.

Could someone advise how I acheive this, the building needs to be matched against its price.

Then I need to total all prices

I also have another field which is called report price which must always go at the bottom of the list just before total.

Any ideas please

Thanks

 

[MarkK]

There shouldn't be more than one thing in one row. If data is missing then don't add a row. If you have a Price column, then summing the values in that column is very simple.
Cheers,
Mark

 

[boblarson]

I concur with Mark. You should not be having multiple fields for the prices. They should be in a junction table as ROWS - one for each price and then the BuildingID as the second part of that table:

BuildingID
Price

and if you need an identifier for what the price is you can add that as well

BuildingID
Price
PriceTypeID

where PriceTypeID would be the ID of the price type from another table

PriceTypeID - Autonumber (PK)
PriceTypeDesc - Text

 

[colkas]

Thanks for the replies guys, I think I fonally got a solution but not ideal.

I have like you say ID, Descritption and price. int he forms and reports I am just totalling the price. Also I have added in quoteid so it lnks in the quote it is in as a sub form.

If I wanted to make the description into a combo box, it may be that the free text description may go ---- I assume I would make the combo selection from another table and then link the 2 tables together is this correct?

Thanks for the replies

 

[boblarson]

I'm confused by your explanation (sorry about that). Can you post the database so we can see what you are trying to describe?

 

[colkas]

Hi Bob

Not as confused as me ha ha

 

[colkas]

Im that confused I didnt fincish my thread.

I have attached the test db I have been playing with today. In it is form called formquote and here you will see the subform. I only have the quote working for type combination at the moment. in the last page of the report (use report button) you can also see the subreport in there if you need to.

In the sbuform you can see it is all free text to enter building description and price etc.......

If moving forward I wanted the user to select a type of something instead of typing in all the time, how would I acheive this.

In my normal live DB I am using access as a front end for MySql but of course any required table can easily be added and linked.

Thanks for looking

Sorry its not allowing me to load it on here I ahve uploaded it ot something called mediafire. Here is the lnk, i foyu cant get it let me know and I will give this site another go to upload it.

http://www.mediafire.com/file/wh1epb8dg8008t2/testdb.accdb

Thanks again

 

[boblarson]

Did you run Compact and Repair and then ZIP the file before attempting to upload here? I can't access the file from work as that site is blocked and I may not get to it tonight as tonight is our grocery shopping night which usually takes my wife and I several hours and by the time I get home it is almost time for bed.

If you can try compacting and then zipping it and can upload it here that would be good. Otherwise, maybe someone else will come along and be able to before I can do it later tonight.

 

[colkas]

I did zip it, I ahve no cpmapct and zipped it. It trys loading it and then says (see below) not sure what that means, it is 15.2 MB though. Hopefully you can get it this evening, good luck with the shopping....

Your submission could not be processed because a security token was missing.

If this occurred unexpectedly, please inform the administrator and describe the action you performed before you received this error.
​

 

[colkas]

Hi Bob

I dont suppose you managed to get the download of my DB did you from the link I sent. I tried sending it again on here but got same result.

Also if oyu manage to get the link, in the quoteform the subform for the prices, there is a totsl price field in the footer of the form. I cannto get it to show on the screen just the rows. Any ideas how I can get it to show please.

Thanks

 

[boblarson]

Well I did download the database but it has nothing in it which you have mentioned. There is no quote form and no prices.

Oh, and by the way with this one it was 3.43 Mb but when I used Compact and Repair it took it to 476 Kb and zipping it took it to 27 Kb which is well under the size to upload here.

 

[colkas]

Hi Bob

Sorry about that I must have supplied the incorrect link, the DB is 113mb. heres the correct link I beleive

http://www.mediafire.com/file/317n89mgzuzc12w/Access%20test%20-%20Copy.zip

Thanks