Need help on inserting record into a table 1

[DBServices]

Hello, I am building a database for a barber shop. Not too complicated and it's coming along well. I have an unbound form that has option buttons for user to select types of haircuts, prices, and payment methods. After all fields are populated, user clicks cmdSubmit button. This runs an Insert Into query and stores all data in my tblHaircuts. At the end of the day, after the last haircut is done, I want to run an "End of Day" function that will take the total amount of money brought in for that day and insert a line into my tblLedger for financial reports. I know I need to run a query to get the total of that day and I have that, qryDailyTotals. This brings back the total sales grouped by day. I need to get that total into the tblLedger, do I need to dim a record set? Or do I just need to run an Insert Into query? If I do that, how do I insert the total amount the qryDailyTotals is pulling back into the tblLedger? Any and all help would be greatly appreciated! Thank you, Dannie.

 

[dhookom]

An insert query should work. Can you share the qryDailyTotals SQL view as well as the structure of tblLedger?

Duane
Hook'D on Access
MS Access MVP

 

[DBServices]

Sure, and thank you for helping.
Here's the SQL statement: SELECT tblHaircuts.HaircutDate, Sum(tblHaircuts.TotalPrice) AS SumOfTotalPrice
FROM tblHaircuts
WHERE (((tblHaircuts.EmployeeID)=1))
GROUP BY tblHaircuts.HaircutDate;
This brings back totals for each day for each employee. The way I have it going is each haircut is simply a record inserted into the tblHaircuts. This is to track the haircutType, haircutDate, haircutPrice, and which employee is cutting hair. The qryDailyTotals is simply summing up how much they brought in that day. Then I want to take that total and insert it into the tblLedger at the end of the day for financial reporting. The tblLedger has the following fields:
"LedgerID, LedgerDate, IncomeType, DebitIn, CreditOut", and a few other fields for making payments. The LedgerId and the LedgerDate are just the primary key and the date the record was entered into the tblLedger. The IncomeType is the type of income, such as product sales, haircuts, refunds, etc.; and the DebitIn and CreditOut are the amounts of money coming in or going out.
The ledger table is what I am using to track accounts Receivable/Payable. I am creating a budget and P&L Statements as well as numbered accounts. I hope this was helpful...Thankyou!

 

[dhookom]

You need to add a column to your qryDailyTotals that feeds into the IncomeType field in the ledger table.

SELECT tblHaircuts.HaircutDate, "Haircut" as IncomeType, Sum(tblHaircuts.TotalPrice) AS SumOfTotalPrice
 FROM tblHaircuts
 WHERE (((tblHaircuts.EmployeeID)=1))
 GROUP BY tblHaircuts.HaircutDate;

You will need to avoid duplicates by using an outer join from qryDailyTotals to the ledger table on the incometype and LedgerDate fields and filtering where LedgerID is Null. Only then can you append to the ledger table.

I assume your previous daily totals won't change or else you would need to worry about recalculating the values.

I would also question why you have a "haircuts" table. Do you also have product sales, coloring, permanent, and other tables? Typically I would recommend a single table with an IncomeType field.

All totals should be available in your transactional tables but I guess rolling up totals into a separate table is often done in accounting.

Duane
Hook'D on Access
MS Access MVP

 

[DBServices]

Previous totals won't change but if the need arises, I can make an Update Query to handle that. The haircut table just stores what we've done for each customer. At first it started out as only a barber shop, but now a lady works there doing styles, perms, and coloring for females as well and that's what the HaircutType field is for. There is a tblHaircutType which holds all types such as haircut, style, perm, coloring, etc... with a relationship to the tblHaircuts. The tblLedger has a relationship with the tblIncomeType, sorry, I forgot to mention that. We sell salon products as well as cutting and styling hair so I needed a way to describe how the money was coming in. So, do I dim a variable to hold the total amount for each day the qryTotalSales brings back and then use that in my SQL statement when inserting the record into the tblLedger? How do I assign a variable the amount?

 

[dhookom]

After modifying your original query as suggested, test this select query:

SELECT HaircutDate, IncomeType, SumOfTotalPrice
FROM qryDailyTotals 
 LEFT JOIN tblLedger on HairCutDate = LedgerDate
 AND qryDailyTotals.IncomeType = tblLedger.IncomeType
WHERE LedgerID IS NULL;

If you only see new dates, change your query to:

INSERT INTO tblLedger (LedgerDate, IncomeType, DebitIn)
SELECT HaircutDate, IncomeType, SumOfTotalPrice
FROM qryDailyTotals
 LEFT JOIN tblLedger on HairCutDate = LedgerDate AND
 qryDailyTotals.IncomeType = tblLedger.IncomeType
WHERE LedgerID IS NULL;

Duane
Hook'D on Access
MS Access MVP

 

[DBServices]

I got it to work, thanks so much!!