I am trying my best to give you as much info as possible which is clear and makes sense.
I thought i clearly explained about the forms and sub forms i have, icluding there record source....
Eeek
Last Activity: Feb 24 '07, 10:41 PM
Joined: Feb 9 '07
Location:
-
Yeah, thanks, you can mail it here: u2sneppahtihs at hotmail dot com...Leave a comment:
-
I have:
a main form (frmCustomers) - tblCustomers.
This would have a subform (frmOrders) - tblOrders.
This main form has another subform (frmOrderLine) - tblOrderLine.
What i wish to do is be able to calculate sub totals and totals as users select the product (a combo box linked to ProductID in tblOrderLine) and Quantity also from the same table....Leave a comment:
-
Recrd Source for sub form tblOrderLine: tblOrderLine
Recrd Source for sub form tblOrderLine: tblOrders
Recrd Source for main form frmOrder: tblCustomers...Leave a comment:
-
Right, ok, i changed the SQL to what you provided and that didnt change a thing so it's going OK
on the OrderLine form (which is the subform) how do i get the record source?
I thought if the record source was tblOrderLine (which im assuming it is) and i changed that to SQL it wouldn't work?! Ok, maybe i'm wrong.
The main form is frmOrder which sub form tblOrderLine and sub form again tblOrder
if theres anything more you...Leave a comment:
-
Oh, and also on my form frmOrder it has a sub form tblOrderLine, which has a combo box which displays make and model of the tblProducts and is linked to the ProductID, and the quantity is a text box, so would the sub total field be a text box or ...?
and also how would it look up Price from tblProductsLeave a comment:
-
Yep.
I have done a query
Which does calculate the sub total, however, i would like to display this in a form and a report, also, how would the sub totals all be totalled together?!...Code:SELECT [Price]*[Quantity] AS [Sub Total] FROM tblProducts INNER JOIN (tblOrders INNER JOIN tblOrderLine ON tblOrders.OrderID = tblOrderLine.OrderID) ON tblProducts.ProductID = tblOrderLine.ProductID;Leave a comment:
-
[QUOTE=NeoPa]Good answers to all questions.
Now I'm going to post the full set in a separate post. When you need to reuse it, click on Reply and copy the resulting text (this will include all the display formatting) except the
No, OrderLineID is a primary key field on it's own, i don't have any composite keys if thats what your referring to :-)...Leave a comment:
-
OrderLineID is a Primary key, autonumber and productID is the same
and sorry i made a mistake about OrderLineID in the tblOrders it's OrderID in OrderLine table :-)
and no i do not want to store any calculated fields, its becoming way to complicated if i do. i guess it's easier the other way every1 recommends., im new to this so i guess if its recommended it's probably the best way. :-)...Leave a comment:
-
I guess it's because i'm new to forum stuff and Access that i didnt realise the amount of detail needed to explain the structure properly, as far as the querying database by date problem, it's sorted. thanks a lot guys....Leave a comment:
-
Code:[b]Table Name=tblCustomers[/b] Customer ID; Autonumber; PK title; Text forename; Text surname; Text (address details); All Text email address; Text
Code:[b]Table Name=tblOrders[/b] orderID; Autonumber; PK date; Date/Time OrderLineID (WHAT ELSE?) CustomerID; foreign key!; FK
...Code:[b]Table Name=tblOrderLine[/b] OrderID; FOREIGN KEY; PK & FK (I've added this as it's surely
Leave a comment:
-
Has put you off? I've tried explaining as much as i can. As i've said before i am not familiar with Access i have asked on here whether or not i should store calculated fields, at the moment they are stored fields because no1 has said any other reason why i shouldnt have them.
I have an OrderLineID as the OrderLine table allows users to purchase more than one product under one order record.
and no, no coding so far has he...Leave a comment:
-
Ok, Well let me try and explain the database a little better:
tblOrders contains Date, OrderID, OrderLineID and Total
tblCustomers contains: CustomerID, Title,Forename, Surname and all the rest of the details...is this any clearer???...Leave a comment:
-
I have a customers table with the following:
Customer ID
title
forename
surname
(address details)
email address
this is linked (one to many) to tblOrders; with:
orderID
date
OrderLineID
CustomerID
Total <--- SHOULD I STORE A CALCULATED FIELD
tblOrderLine
OrderLineID
Quantity
ProductID
Sub Total
...Leave a comment:
-
Sorry, I'm very very new to Queries, could you give me some more info please, i need to save all of these sub totals to give me a Total for the tblOrders, how would i do this, or would i need to store this field?Leave a comment:
-
How would this work?
I have one field ProductType in the table tblProducts.Leave a comment:
-
Thanks Mary but this isn't working, this asks me to enter a customer ID of some sort, i would like it to work so that a list of customers are provided that havent placed orders in the last 3 months, with no need for any entry....Leave a comment:
-
Duplicates in Combo Boxes
OK, I really don't know how to figure this one out; I have a form (tblOrderLine) in which users can select products (from tblProducts) and enter a quantity; however, there are different types of products (mobile phones and MP3 players), so i have a combo box that (combo129) that is linked to the field Producttype (in tblProducts) however it displays like this:
mobile phone
mobile phone
mobile phone
mobile phone... -
Filtering Combo Boxes/ No Duplicates
I have a form (frmOrder) which uses a subform, tblOrderLine, the field 'Product Type' is stored in tblProducts, I have a combo box (cboPType) which is linked to the product type field in tblProducts, however it contains repeated types (i.e. 30 books, 5 DVD's); I would like to filter the next combo box (combo68) which contains a list of products (linked to tblProducts) and, i would like to filter the combo box, so that if, say books are selected in... -
Also, this doesn't work!
I tried this, i do not know which field this code would come under, please help!Leave a comment:
No activity results to display
Leave a comment: