Not sure how to make this work

  • Thread starter Thread starter Adien
  • Start date Start date
A

Adien

What I want is a form where you enter the week # and year, and the
total costs and sales for that week/year would be outputted.

What I have is 3 tables. One that has all of the sales info on a daily
basis. One that has the invoices that have come in (so there can be
numerious invoices on any given day). And a Dates table that if
combined in a query with either the costs tbl or the sales tbl will
assign the proper week number to each entry.

Thanks
Adien
 
Adien,

What do you want to do with the first and last weeks of the year. If the
year starts on a Wed, are you only interested in the sales and invoice values
for Wed - Sat?

How do you want to output this data? If an unbound textbox on the form?

I would create a small subroutine in the forms code module. Then, in the
AfterUpdate event of the txt_Week and txt_Year textboxes, I would call this
subroutine:

Private txt_Week_AfterUpdate

Call WeeksSalesAndInvoices

Something like:

Private Sub WeeksSalesAndInvoices

Dim strCriteria as string

if LEN(me.txt_Week & "") = 0 then
msgbox "Enter the week number!"
me.txt_Week.setfocus
exit sub
elseif LEN(me.txt_Year & "") = 0 then
msgbox "Enter the year number!"
me.txt_Year.setfocus
exit sub
else
me.txt_SalesTotal = DSUM("Amount", "qry_Sales")
me.txt_InvoicesTotal = DSUM("Amount", "qry_Invoices")
endif

End Sub

Then, create two queries that look similar to:

qry_Sales:
SELECT SUM(tbl_Sales.AMOUNT) as Amount
FROM tblSales INNER JOIN tbl_Dates
ON tblSales.SalesDate = tbl_Dates.DateVal
WHERE tbl_Dates.Year = FORMS!YourFormName.txt_Year
AND tbl_Dates.Week = Forms!YourFormName.txt_Week

qry_Invoices:
SELECT Sum(tbl_Invoices.Amount) as Amount
FROM tbl_Invoices INNER JOIN tbl_Dates
ON tbl_Invoices.InvoiceDate = tbl_Dates.DateVal
WHERE tbl_Dates.Year = FORMS!YourFormName.txt_Year
AND tbl_Dates.Week = Forms!YourFormName.txt_Week

HTH
Dale
 
Dale,

Thanks a ton. That was exactly what I needed. I put all the info into
my database (altered as needed of course) and it's working perfectly.

Adien
 
Back
Top