INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need help on inserting record into a table

Need help on inserting record into a table

(OP)
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.

RE: Need help on inserting record into a table

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

RE: Need help on inserting record into a table

(OP)
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!

RE: Need help on inserting record into a table

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

CODE --> SQL

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

RE: Need help on inserting record into a table

(OP)
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?

RE: Need help on inserting record into a table

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

CODE --> SQL

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:

CODE --> SQL$

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

RE: Need help on inserting record into a table

(OP)
I got it to work, thanks so much!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close