-
Jan 9th, 2018, 04:24 PM
#1
Thread Starter
Hyperactive Member
To modify code to get some calculations in 2 additional columns using Select query ..
How to modify the following code to get the result as in the attached image. Here two new columns to be added to the DataGridView with some calculations (which is hard part since it require IF condition in Select statement).
Need two more fields: ______ As TaxAmount, _______PriceWithTax
Price field is hard for me. Because, if the box value is more than zero, then the price is "One Box Price" or if the box value is zero then it is "One Piece Price". How to modify the code to get New Fields TaxAmount & PriceWithTax?
Code:
Dim StrSqlItems As String = "Select ModelNumber, Box, PackingPerBox, Quantity, Price, TotalAmount, TaxRate from ItemsDatabaseTable ORDER BY ModelNumber ASC"
Last edited by VS2013; Jan 9th, 2018 at 04:34 PM.
-
Jan 10th, 2018, 04:36 AM
#2
Re: To modify code to get some calculations in 2 additional columns using Select quer
Hi,
goes like this...
I put 19% Tax in my Query
Code:
SELECT Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, ([Order Details].[UnitPrice]*[Quantity]) AS TotalPrice, Format([TotalPrice]/100*19,"#,##0.00") AS xTaxFromTotal, [TotalPrice]+[xTaxFromTotal] AS TotalAll
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID;
regards
Chris
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jan 10th, 2018, 11:44 AM
#3
Re: To modify code to get some calculations in 2 additional columns using Select quer
Price field is hard for me. Because, if the box value is more than zero, then the price is "One Box Price" or if the box value is zero then it is "One Piece Price". How to modify the code to get New Fields TaxAmount & PriceWithTax?
the easy solution is having 2 different product numbers for the same product
if sold per box : ProdB
if sold per piece: ProdP
do not put off till tomorrow what you can put off forever
-
Jan 10th, 2018, 12:04 PM
#4
Thread Starter
Hyperactive Member
Re: To modify code to get some calculations in 2 additional columns using Select quer
The problem now is, almost more than 3,000 records were added. Is there any solution to use if condion to resolve this issue?
-
Jan 10th, 2018, 12:20 PM
#5
Re: To modify code to get some calculations in 2 additional columns using Select quer
The problem now is, almost more than 3,000 records were added.
if the simple fact of adding 3,000 records causes a problem
then the real problem is probably bad design
Is there any solution to use if condion to resolve this issue?
resolve what issue ?
do not put off till tomorrow what you can put off forever
-
Jan 10th, 2018, 02:29 PM
#6
Thread Starter
Hyperactive Member
Re: To modify code to get some calculations in 2 additional columns using Select quer
So, you mean there is no way to use the same database (as it is) to achieve the result.
-
Jan 10th, 2018, 03:13 PM
#7
Re: To modify code to get some calculations in 2 additional columns using Select quer
if you mean you want to achieve the result as in your original question in post#1
then ChrisE already told you how in post#2
if you want to achieve something else
then clearly explain what you want to achieve
oh,btw
So, you mean there is no way to use the same database (as it is) to achieve the result.
i, for one, have not the faintest idea of the design of your database
so, i do not have the faintest idea if there is a way to use the same database (as it is) to achieve the result.
also i do not have the faintest idea what you want as the result
do not put off till tomorrow what you can put off forever
-
Jan 11th, 2018, 06:54 AM
#8
Re: To modify code to get some calculations in 2 additional columns using Select quer
Your table in the image looks, well wrong... but maybe I'm reading it wrong.
Code:
SELECT
ModelNumber,
Box,
PackingPerBox,
Quantity,
Price,
TotalAmount,
TaxRate
FROM
ItemsDatabaseTable
ORDER BY
ModelNumber ASC
If the box is > 0 - then the price is box price
if the box is 0 then use the unit price
Your query doesn't say where these two prices are held. I assume its in a prices table with a price field for unit and a price field for box.
Code:
SELECT
sq.ModelNumber,
sq.Box,
sq.PackingPerBox,
sq.Quantity,
sq.BoxPrice,
sq.UnitPrice,
sq.price,
sq.TotalAmount,
sq.TaxRate,
sq.TotalAmount*sq.TaxRate AS TaxAmount,
(sq.TotalAmount*sq.TaxRate)+sq.TotalAmount AS TotalPriceWithTax
FROM
(
SELECT
i.ModelNumber,
i.Box,
i.PackingPerBox,
i.Quantity,
i.BoxPrice,
i.UnitPrice,
CASE
WHEN Box=0 THEN p.unitprice
ELSE p.boxprice
END AS Price,
CASE
WHEN Box=0 THEN p.unitprice * quantity
ELSE p.boxprice * quantity
END AS TotalAmount,
i.TaxRate
FROM
ItemsDatabaseTable AS i INNER JOIN Prices AS p ON i.itemid = p.itemid
) AS sq
The above won't work, same reason as IkkeEnGij stated, we dont know the structure of the tables. But perhaps it will give you an idea how to make it work.
This is only a possible way, and depends on the database you are using (you didnt say which). It would be best to try different ways and you decide which is the best for you so if anyone asks you can explain your decision.
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Jan 11th, 2018, 09:22 AM
#9
Re: To modify code to get some calculations in 2 additional columns using Select quer
Miss moonshine has a small beer shop
so she buys by sixpacks and sels by the bottle
after a while several clients want to buy by sixpack to
so she buys by box(=4 sixpacks) and sels by sixpack and by bottle
after a while several clients want to buy by box to
so she buys by pallet(=60 boxes) and sels by box,by sixpack and by bottle
after a while some clients want to buy by pallet to
etc...
one would think that after a while miss moonshine would be a very wealthy woman
not so...the programmer becomes very wealthy
the program and the database need constantly very expensive updates
the employees using the program need constant re-education
the program becomes so complicated the employees make constantly errors
in the end miss moonshine goes bankrupt
but:
miss shinemoon (miss moonshine's friend)
used the simple solution, that every supermarket on the planet uses
and became a very wealthy woman
do not put off till tomorrow what you can put off forever
-
Jan 12th, 2018, 09:14 AM
#10
Thread Starter
Hyperactive Member
Re: To modify code to get some calculations in 2 additional columns using Select quer
I am going to create a new MS Access Database Tables like in the following images. But I don't know where can I add some extra fields in the Tables to assign Foreign Keys.
-
Jan 12th, 2018, 11:38 AM
#11
Re: To modify code to get some calculations in 2 additional columns using Select quer
I would suggest you take the prices out of the products table, and add a start date... that way should the price change you wont need to rebuild the tables... perhaps will cause other problems
serial no isnt in the products, just sales perhaps, productid (field) replaces the red box. If you add the prices into another lookup table, just put the priceid as another field...
example :
your sales table becomes something like :
Code:
salesid pk
custid fk
deliveryaddress..fields
invoicenro
invoicedate
serialnro
productid fk
priceid fk
taxid fk
discount
Code:
--pricestable--
priceid pk
typeid fk? (table of box, pallet, unit etc..)
iswholesale (true or false) < change to lookup table to if more options
productid
startdate
price
Code:
--tax table--
taxid
startdate
taxperc
This is from your tables btw.. not experience, but I think several people on here do a lot with finance tables, might be better if you ask nicely for them to suggest better table structures (from their experience) and why.
Or do as many different ways to see which cause problems and where...
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Jan 12th, 2018, 12:05 PM
#12
Thread Starter
Hyperactive Member
Re: To modify code to get some calculations in 2 additional columns using Select quer
Thanks for your kind support & time. I will edit tables as per your suggestion.
Actually, I would like to start a New MS Access Database in a professional way (which I don't know until now) to avoid any flaws while getting reports as I was advised to do so in previous posts.
So, it is my humble request to all to guide me in creating a database (for Account purpose of Sales, Purchase & Inventory). I am not fully aware of how many tables that really required to build it.
-
Jan 21st, 2018, 08:18 AM
#13
Re: To modify code to get some calculations in 2 additional columns using Select quer
I am sure a lot of people here are willing to help you
problem is no one knows exactly what you want, or what it is going to be used for
so, if you put here a very detailed descrition of the what, the why, the how
i am sure people will be willing to help you
iow: put here the system analysis you have done
if all you want is
Actually, I would like to start a New MS Access Database in a professional way
actually there are a miriad of books on database design
did you go to the bib ?
did you google for database design ?
do not put off till tomorrow what you can put off forever
-
Jan 24th, 2018, 01:49 PM
#14
Thread Starter
Hyperactive Member
Re: To modify code to get some calculations in 2 additional columns using Select quer
Thanks. Actually, I would like to create a small database for footwear shop to prepare sales invoice, record purchase details and maintain an inventory of stock. So, how can I design my database?
-
Jan 24th, 2018, 08:09 PM
#15
Re: To modify code to get some calculations in 2 additional columns using Select quer
unless someone here has already done a system analysis on a footwear shop, and is willing to put it here
you will have to do the system analysis yourself, and put it here
so tell in minute detail the what, the why, the how, the when of a footwear shop
how many footwear shop owners have you already interviewed to ask just what is needed to operate the shop
have a look at the Northwind sample database maybe will give you some ideas
do not put off till tomorrow what you can put off forever
-
Jan 29th, 2018, 09:00 AM
#16
Re: To modify code to get some calculations in 2 additional columns using Select quer
Have you practiced with normalization?
First analysis is to capture all the fields you need. If there are any pre-defined reports that are required keep them close so you can confirm all the fields you write down are in the reports somewhere. Plus other fields not in the reports. Any source files? Add those fields.
Apply normalization to the fields to group them together. Apply a second time; to further reduce holding duplicate data. These should give you a base for tables.
Add primary keys.
Add foreign keys where applicable or in separate tables to point one to the other (depending on the business rules).
Try manually putting in one or two records and see if the structure works, If so - start on your forms.
Rest is practicing how to hold data, best option to use for which situation, or favoured structure you prefer. You can only get this from experience... Best to try as many different ways as possible to see which works better for you and still gives good/accurate results..
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Jan 30th, 2018, 07:26 AM
#17
Fanatic Member
Re: To modify code to get some calculations in 2 additional columns using Select quer
as with any sales database you will need the following tables.... this is the basics only
-Stock, items, item count, item description
-Sales, item sold, amount sold, price sold per item, customer sold too, date sold
-purchases, item bought, amount bought, supplier bought from, price paid
in reality you will have several other tables..... these tables will link suppliers, customers and then you will have preferably tables linking exactly which products you sold and how much you paid for them, etc etc ....
it something you need to right down on paper and plan out, just take your needs, come up with a plan to record those needs and build what you need to do that.
Yes!!!
Working from home is so much better than working in an office...
Nothing can beat the combined stress of getting your work done on time whilst
1. one toddler keeps pressing your AVR's power button
2. one baby keeps crying for milk
3. one child keeps running in and out of the house screaming and shouting
4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
5. working at 1 O'clock in the morning because nobody is awake at that time
6. being grossly underpaid for all your hard work
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|