Getting sum of child's field. (1 Viewer)

[Flashbond]

Bill_ID(PK)--------Name--------------Address-----------Total
1683---------------Anakin Skywalker---Tatooine------------500$
------------------+ Product_ID(PK)---Bill_ID(FK)-----ProductName----------------------Price
--------------------23-----------------1683------------Super Cooled Galactic Laser Shield---200$
--------------------28-----------------1683------------Turbo Boosted Hyper-Jump Engine---300$

Hi Guys!

I am very new to Access.
Can some tell me how to get "Total" field of Bill table with sum of child Product table's "Price" field?

Thanks a lot!

 

[Cronk]

I hope that when you refer to Child, you mean a child subform.

If so, add a text box with the control source set to
=Sum([Bill]) where Bill is the field that stores the amount of the individual items.

 

[CJ_London]

Just to add to Cronk's post

If so, add a text box with the control source set to
=Sum([Bill]) where Bill is the field that stores the amount of the individual items.

The control source needs to be in the form or report footer

 

[Flashbond]

No, I didn't mean subform. s
Subrecords of a table as FKs depended on PKs.
I have only tables. Should I have to create forms?

 

[CJ_London]

if you just want a total in a query then the following sql will do what you require:

SELECT BillTbl.Bill_ID, BillTbl.Name, Sum(ProductTbl.Price) as Total 
FROM BillTbl INNER JOIN ProductTbl ON BillTbl.Bill_ID=ProductTbl.BillID
GROUP BY BillTbl.Bill_ID, BillTbl.Name

Note that Name is a reserved word and can produce unexpected results so recommend you change the name of this field - here is a link to access reserved words http://support.microsoft.com/kb/286335

It may be you have simplified your data for the purposes of your thread, but normally I would expect to see quantity and price fields and you would then calculate the value. Similarly, you would not normally store the total in the Bill table but calculate it as required

 

[Flashbond]

Hmm.. It's another thing, then... You say It's bad design. You recommend to calculate if it is needed.
Can you give me a better design example with a query please? I mean to calculate Total later...

 

[CJ_London]

Im not quite sure you are starting from, but I would expect you will have as a minimum four tables along the following lines

tblCustomer
CustomerID Autonumber PK
CustomerName Text
CustomerAddress Text
...
...

tblProducts
ProductID Autonumber PK
ProductName Text
ProductPrice Currency
...
...

tblBillHeader
BillID Autonumber PK
CustomerID Long FK
BillDate Date

tblBillDetail
DetailID Autonumber PK
ProductID Long FK
BillPrice Currency
Quantity Long (note this may need to be double if dealing with part quantities such as 2.4 metres)

 

[Flashbond]

Ahaaa! Super CJ! I think I am on the right way. This database will be on my local business server to track financial stuff. And I will manage it via VB form. I am writing it in .Net separately. Infact, beside this simple example, I am happy because I designed my actual database just as you suggested.

I have tblcutomers which has customers personal contact infos.
I have a tblbillinginfos table. You may define multiple billing infos(such as address and tax number) for one customer.
I have tblproducts which includes airways ticket details with pices (I own a travel company).
At this point, there is something exceptionary about my business. You can not have pre-ready products table something like "all the air tickets in the world". Because different destinations, different airlines and dates... You know, there is a zillion of probabilities. So I will input the tickets to this table when each customer come to my office to buy one. Do you think, this is Ok?

The only thing I made different than you had suggested was, I tried the design the tblbills as AIO table. With header information like billing date, and time (we both need in Turkey). I get the other things from the other tables like billing name, address, product details, product price, etc... But all in one table.

Now you say, AFA I understand, to split my tblbilling into two, like upper part of the invoice(header) and the lower part(purchasing details).

And like this way, I will be able to calculate items in tblBillDetail easly within each table!

Then, one last question; would you suggest me to combine these two tables later into one like tblBills? Because I am not very good with this stuff and when I am reporting, I want to deal with only one table which is a, let say, "complete" bill. (I guess you'll say No, because I have all the parts of the puzzle already...).

Thanks a lot for everything. I gave me a wider vision.

 

[CJ_London]

Ah, travel agency! I agree slightly different situation - helped to develop one once (german system but used in UK)

This link might give you some ideas http://www.databaseanswers.org/data_models/airline_reservations/index.htm

In that case I would suggest you do not need a product table but a group of linked tables which would be along the following lines with tblPackage being the equivalent of tblProduct:

tblItemType
ItemTypeID autonumber PK
ItemTypeDesc text (airline, rail, taxi, bus, parking, hotel....etc)

tblItem
ItemID autonumber PK
ItemDesc text (flight, cabin, couchette, train, taxi...etc)
ItemStandard text (1st Class, Business Class, Standard....etc)

tblSupplier
SupplierID autonumber PK
SupplierName text
SupplierAddress text
...
...

tblSupplierTypes
SupplierTypeID autonumber PK
SupplierID long FK
ItemTypeID long FK

tblPackage
PackageID autonumber PK
SupplierID Long FK
ItemID Long FK
DateFrom Date
NoDays Long
Price Currency

The object of the exercise is to not repeat data - otherwise if you change it in one place you have to change it elsewhere as well. Having said that there are exceptions if you have data that changes over time (like prices) but a table which requires a fixed view (like an invoice price) then the value is duplicated - but as time goes on, the invoice will retain the original price whilst the product increases in price.

The trick is to not think like an Excel spreadsheet! Keep your data in the smallest common elements and combine them in a query to create the view that you want

 

[Flashbond]

The trick is to not think like an Excel spreadsheet!

This will be my motto! So, I'll think alwas inductive. Building up the whole from parts... Thanks for your long and patient explanations

 

[CJ_London]

Clicking the Thanks button is thanks enough!