Newbie with some forms questions

[Dennis9412]

Hi There.

I am a real newbie with absolutely no access experience, trying to muddle my way through setting up a work-order type database.

I have set up a table for work-orders with a general description of the repairs affected. I have a form set up to enter the info, which contains a tab control which has a tab for parts and another tab for labor. So a person entering a work-order can enter both parts and labor using the tabs. How do I total the parts and labour and be able to put those totals in a report?

Thanks for any assistance you can give me.

Regards,

Dennis

 

[plog]

This isn't really a form question, its a data question. And for those we need specifics of your table. So, working with what you have given I can give general answer:

I have set up a table for work-orders with a general description of the repairs affected

If a work order can have many expenses attached to it you need another table with this structure:

tblWorkOrderItems
item_ID, autonumber, primary key of table
WorkOrderID, number, foreign key to Work orders table
item_Type, text, will hold if this is Labor or Part (and any other type you may have)
item_Cost, number, cost of the item

That's the broad strokes, your table may have more fields depending on what else you want to capture. Then for the total, in a query you would use this SQL:

SELECT WorkOrderID, SUM(item_Cost) AS WorkOrderTotal
FROM tblWorkOrderItems
GROUP BY WorkOrderID

If you could provide a better description of your table and what you want your report to ultimately hold, I can be more specific.

 

[Ranman256]

queries. The subform entering labor can be totaled in the subform footer.
in the footer put a text box and set the rowsource = SUM(AMT)
(or whatever you want totaled)
It will show on the form.

Do the same with reports. The query pull the records you want, and set some fields
=sum(amt)

 

[Dennis9412]

Thank you for the quick reply plog.

As I stated, I am a virtual newbie in Access and have never been involved in setting up a relational database, so bear with me.

This database will be for my sons small trucking company (only 5 units so far).

It starts with a Vehicle table which is linked to a repair order table (one to many),

The repair order table has links (one to many) to both a repair order parts table and a repair order labour table.The repair order parts table is linked (one to many) to a parts table and the parts are selected by a lookup in the repair order parts table.

The reason I thought it was a form issue is both the repair order parts and labour table are shown as subforms on a tab control in the RepairOrdersForm, and thats where I'm trying to get a total for both parts and labour from. One problem I'm having is totaling the parts. I do the =sum([Quantity]*[Unitcost])in the repairorderparts footer, but it only does the calculation for 1 part record. If the repair order has 3 parts, it doesn/t include them.

I hope you have enough info now to assist. If you need more info, please let me know.

Thanks,

Dennis

 

[Dennis9412]

Thanks plog. I have that other table already, see attached png file.

I would like the report to list all repair orders by unit number with totals for parts,totals for labour and a grand total for each repair order.

How do I enter the SQL statement into a query?

 

[Dennis9412]

Thank you Ranman256. How do I total the parts subform? If I have more than 1 part associated with the repair and I do the =sum([Quantity]*[UnitPrice]) in the subform footer, I only get an answer for 1 part. What am I missing?