telsales orders and other...

(1) I have a table containing SalesID, customer, type, qty, price, deliveryaddress, paymentmethod, paymenttype.

I need to break this table down but I'm not sure which fields should stay with which?

(2) order/invoice form - calculating costs, payment due, date and the ablility to print/preview. Would this be a form version of the about table.

(3) summary reports - showing sales by customer, employee or date. reports can be generated by selecting any on the above 3 methods. how would I do this? Do I set up queries based on the sales table/form?

please help

I would look at the sample Northwinds database for several good examples on table and relationship layout. What you are asking is exactly what this sample database does.

lwells

thank you i have looked at northwind.

Problems:

when i try opening the orders form I'm asked for parameter qualities for all my files in my query. I have a feeling that QryOrder is wrong some how but i don't know how?

when I try to open a report from the SalesReport form I'm told there is a time out error and part of my expression can't be found.

I have placed a look up column for productID in my order details table. The ID's come from the product table, but the Supplier ID comes up not Product ID.

I can view the report (sales by region) when selecting it in as a report, but i can't when I try accessing it via switchboard - reports - sales by region. I get a time out error - 'can't find the field "..." referred to in your expression'

For the orders form (invoice) I have no idea when I've done wrong with the query - SELECT tblOrders.OrderID AS Expr1, tblOrders.CustomerID AS Expr2, tblOrders.EmployeeID AS Expr3, tblOrders.OrderDate AS Expr4, tblOrders.DeliveryAddress AS Expr10, tblOrders.DeliveryCity AS Expr11, tblOrders.DeliveryRegion AS Expr12, tblOrders.DeliveryPostCode AS Expr13, tblOrders.Country AS Expr14, tblCustomers.Address1 AS Expr16, tblCustomers.Address2 AS Expr17, tblCustomers.Region AS Expr18, tblCustomers.PostCode AS Expr19, tblCustomers.Country AS Expr20
FROM tblCustomer INNER JOIN tblOrders ON tblCustomer.CustomerID = tblOrders.CustomerID;

orders subform - SELECT tblOrderDetails.OrderID, tblOrderDetails.ProductID, tblProducts.ProductName, tblOrderDetails.UnitPrice, tblOrderDetails.Quantity, tblOrderDetails!UnitPrice*tblOrderDetails!Quantity AS ExtendedPrice
FROM tblProducts INNER JOIN tblOrderDetails ON (tblProducts.ProductID = tblOrderDetails.ProductID) AND (tblProducts.ProductID = tblOrderDetails.ProductID)
ORDER BY tblOrderDetails.OrderID;

so i no longer have a problem with my query being parameter just the fact that i get an error saying form supplied in form/report does not exist. I have looked at the 'record source' for both form and subform & the data is currect (i think).

can some please sorts out frmOrders for me? I can't get it right

Hi babybear,

Try this:

frmOrders

SELECT tblOrders.OrderID, tblOrders.CustomerID, tblOrders.EmployeeID, tblOrders.OrderDate, tblOrders.DeliveryAddress, tblOrders.DeliveryCity, tblOrders.DeliveryRegion, tblOrders.DeliveryPostalCode, tblOrders.Country, tblCustomers.Address1, tblCustomers.Address2,tblCustomers.Region, tblCustomers.PostalCode, tblCustomers.Country
FROM tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerID
ORDER BY tblOrders.OrderID;

orders subform

SELECT tblOrderDetails.OrderID, tblOrderDetails.ProductID, tblOrderDetails.UnitPrice, tblOrderDetails.Quantity, tblProducts.ProductName, (tblOrderDetails!UnitPrice*tblOrderDetails!Quantit y) AS [ExtendedPrice]
FROM tblProducts INNER JOIN tblOrderDetails ON tblProducts.ProductID = tblOrderDetails.ProductID;

Link Child - Master Fields on OrderID

lwells