Create form from query and group by…help!

[infinite2006]

using Access 2010



Hi guys,

My aim is to create a customer price quotation form and accompanying report for printing the customer quote. Thus far I’ve created two tables with a one to many relationship (one customer can have many orders). Please see attached. I think this part is sound although a more efficient design may have called for a third table.

My thinking is that if I create a query that pulls in all the fields then I can use this query to build a solid form for producing customer quotations. One of the fields (markup %) is parameter based so it will prompt the user to enter a percentage markup in order to calculate the sales price based on the cost price, for example 10, 20% etc. I think I’ve got this part working also, please see the query design and output, but not sure if the prompt will be generated when I build the form from the query?

Question 1: Am I on the right track in terms of building a form from the above query, does this make sense? If not, ignore question 2, it’s irrelevant and back to the drawing board.

Question 2: I would like to be able to sum/total each INDIVIDUAL customer order. Presently I cannot get this working, when I add totals to the query or ‘group by’, the total of ALL customer orders is presented. Is my table design totally flawed, what silly mistake am I making?

Question 3: Can anyone shed some light on the above in relation to generating a report from this form, perhaps this is where the group by is best placed?

Many thanks,


Infinite

 

[CJ_London]

Some initial thoughts

1. you need a minumum of 3 tables not 2.

1. Customers
2. Quote headers
3. Quote detail

You probably need additional ones for

Materials
Employees
Labour rates

Am I on the right track in terms of building a form from the above query, does this make sense? If not, ignore question 2, it’s irrelevant and back to the drawing board

From the above - No. You need a main form for the quote header using just the quote header table and a subform for the detail using just the quote detail table.

I

would like to be able to sum/total each INDIVIDUAL customer order. Presently I cannot get this working, when I add totals to the query or ‘group by’, the total of ALL customer orders is presented. Is my table design totally flawed, what silly mistake am I making?

Without seeing your query, difficult to comment, At the moment there is nothing in your data to indicate an order with more than one item other than the orderddate - what happens if a customer places 2 orders on the same day?

Can anyone shed some light on the above in relation to generating a report from this form, perhaps this is where the group by is best placed?

Suggest you get the above issues resolved first before addressing this

Finally, what is the preview field? generally best not to have OLE objects in a db

 

[infinite2006]

CJ_London, thanks for the fast reply. Its back to the drawing board then, can I ask a few more pointers (literal instructions, truth be told) before I make another mess of things and end up back on the forum?

Firstly, agreed on the report, if I can get the main form up and running I’ll be more than happy. The preview field is supposed to display a picture of the product item as part of the customer quote but again this is perhaps over ambitious, I read that an linked object was the way to do this but similarly if I can get the main part working, it will be fine, so ignore for now.

Do you think I need six tables then? I’ll attempt to give a breakdown of the fields and relationships, hope you agree, albeit not much to agree on. Well spotted on the differentiation of items, related to an order, date is clearly not sufficient.

*keyfields in bold

1. Customers (CustomerId, CompanyName, ContactName, Phone, OrderDate)
2. Quote headers (OrderID, CustomerId, PartID, MaterialDescription, Quantity, ItemCost)
3. Quote detail (PartID, OrderID, MaterialDescription, Quantity, Im guessing from here, sorry)

4. Materials (not a clue)
5. Employees (not a clue)
6. Labour rates (not a clue)


Am I right in assuming that the cost price, markup% and sale price will all be calculated fields and no need to appear in the tables? Ive attached a picture of how things should look

 

[CJ_London]

I'll see what I can do to help.

Some questions first though.

1. Since this data is also used to produce a quote so be sent to the customer - you probably need both customer address and delivery address, so an address table as well.

2. Will this data also be used to create an invoice? if so, more work here! Need to think about VAT

3. Not sure from your attachment what you are doing with paint (2*£6.50=£13.00)

4. With regards item cost - is this something you look up each time or could the data be in the material table?

Corrections to your table design - note PK means Primary Key and FK means Family (or Foreign) Key. Primary Key uniquely identifies the record within a table, Family Key identifies records in other tables that 'belong' to that Key (called a one to many relationship). e.g. one company has many quotes, one quote has many lines. One product also has many lines

1. Customers (CompanyPK, CompanyNo, CompanyName, ContactName, Phone)
2. Addresses (AddressPK,CompanyFK, AddrType(Billing/Delivery), AddrLine1, AddrLine2,etc)
3. Quote headers (OrderPK, OrderNo, CompanyFK, (Delivery)AddressFK, OrderDate)
4. Quote Lines (LinePK, OrderFK, LineNo, PartFK, Quantity, Price)

5. Materials (PartPK, PartNo, PartDescription, Price)
6. Employees (not a clue) for the quote, this is only required if you have different labour rates or if you want to restrict access to the system to certain employees
7. Labour rates (not a clue) as for employees

Note the difference between CompanyPK and CompanyNo. The first is just an identifier for the record, the second is an identifier for the company (might be an account number from your accounts system for example)

Enough to be getting on with - try putting this together and we'll see where we go from there

 

[infinite2006]

A massive thank you, really couldn’t have visualised the table design and agree I have plenty to be getting on with as regards implementing all the required changes. Once I’ve worked my way through this lot, I’ll repost the table design (inc relationships) as a actual database so you can give it the nod before I try creating the form, which I know can be a time consuming task, aligning fields and so forth. Many, many thanks again. Some feedback below

Some questions first though.

1. Since this data is also used to produce a quote so be sent to the customer - you probably need both customer address and delivery address, so an address table as well.

Correct, see you’ve already included, thanks.

2. Will this data also be used to create an invoice? if so, more work here! Need to think about VAT

Will work on that later, after a bit of work on the main form first of all.

3. Not sure from your attachment what you are doing with paint (2*£6.50=£13.00)

Paint is just another cost, see note G below?

4. With regards item cost - is this something you look up each time or could the data be in the material table?

Whatever is the more robust solution, I see you’ve included in materials table in the design

Corrections to your table design - note PK means Primary Key and FK means Family (or Foreign) Key. Primary Key uniquely identifies the record within a table, Family Key identifies records in other tables that 'belong' to that Key (called a one to many relationship). e.g. one company has many quotes, one quote has many lines. One product also has many lines

1. Customers (CompanyPK, CompanyNo, CompanyName, ContactName, Phone)
2. Addresses (AddressPK,CompanyFK, AddrType(Billing/Delivery), AddrLine1, AddrLine2,etc)
3. Quote headers (OrderPK, OrderNo, CompanyFK, (Delivery)AddressFK, OrderDate)
4. Quote Lines (LinePK, OrderFK, LineNo, PartFK, Quantity, Price)
5. Materials (PartPK, PartNo, PartDescription, Price)

Many thanks for the revised design, this is really what I was incapable of visualising first

6. Employees (not a clue) for the quote, this is only required if you have different labour rates or if you want to restrict access to the system to certain employees

I had planned on including an employee ref (forename or initals), just for identifying who produced the quote so don’t think a table is necessary? Possible to include in Customers table as a text field only? See note A, below?


7. Labour rates (not a clue) as for employees

Simply wanted an additional cost to add in for labour and miscellaneous overheads, I take it this needs another table? Suggestion, F below:

A. Customers (CompanyPK, CompanyNo, CompanyName, ContactName, Phone, EmployeeRef)
B. Addresses (AddressPK,CompanyFK, AddrType(Billing/Delivery), AddrLine1, AddrLine2,etc)
C. Quote headers (OrderPK, OrderNo, CompanyFK, (Delivery)AddressFK, OrderDate)
D. Quote Lines (LinePK, OrderFK, LineNo, PartFK, Quantity, Price)
E. Materials (PartPK, PartNo, PartDescription, Price)
F. Labour (LabourPK, LabourDescription, Price)
G. Paint (PaintPK, PaintDescription, Price)



8. Note the difference between CompanyPK and CompanyNo. The first is just an identifier for the record, the second is an identifier for the company (might be an account number from your accounts system for example)

Yes, fully understand the need here.