ODBC tbl querie or not 1

[MarcoPerez21]

I have a calendar tbl the function for this tbl is to keep the week in month/ year organized for the POS software. my task is get the sales from today and copmare it against last years data on the same period regardless of the day (ie. sales for may 12,03 was a monday, sales fir may 12, 04 is wed)what I need is: (lets say that we are in wk 23 of 2003)week 23 of last year to compare sales. can anyone give insight on which way to go? query or not.


Thanks in advance,
Marco

 

[MikeRBS]

What I would do is write union query that first selects all the records from this period and puts them into one column - perhaps like (id,"0",sales_vol). The second part of the union gets records from one year ago and puts them into another other column - like (id,sales_vol,"0"). Your union then in this case would be like (id,lastyearsvol, thisyrsvol). You can then sum these records as you want for reporting.

If you can't use date functions to get your current date range and your one-year-ago date range then create a table you can join to which matches dates to week/numbers and fill it in by hand.

 

[MarcoPerez21]

Mike,
I am new to access can you give me a coded example to work from? I understand the concept but i havn't the clue as towhat syntax to use. Considering I've only been programming in DB for like 3wks now.


Marco

 

[lespaul]

Marco,

Mike has suggested you write a few queries, doesn't have anything to do with code. With more details from you about your table structure and what you are expecting, someone could write the entire query for you!

So, if you will post back with information about the tables, the fields, what your data looks like and what you want your results to look like we can help you much easier than if we are guessing.

Leslie

 

[MarcoPerez21]

Leslie,
my Pos software has a table called Sy_calendr.
I have a table that I created tbl_daily_sales
in this table I have:
ID
week
period
date
Store_Am_Sales
Store_Pm_Sales
Wholesale_Sales
Store_Praline_Sales
Store_Merch_Sales
Store_Postage_Sales
Wholesale_Praline_Sales
WHolesale_Merch_Sales
Wholesale_Post_Sales
Planned_Store_Hours<-- Is keyed in manually by way of Form
Actual_Store_Hours<--- is keyed in manually by way of Form
Planned_Store_Dollars<- is Keyed in manually by way of Form
Actual_""_""<-- Keyed in Manually by way of Form
Overtime_Dollars
Training_Dollars
AM#_tickets
PM#_tickets
PeakTicketHour
these fields get filled by two ODBC tables that give me the information. the 1st ODBC table gives me the date,time, location, the 2nd table gives me the qty, sales mount,desc of the items sold (ie. pralines, postage, merchandise) I've gotten these two tables to create a "SalesHistoryTbl" it contains all of the sales information since the inception of the POS software.
now from this tbl I have multiple qrys to pump the data into my "Daily_Sales"tbl. the information that is missing right now is the week/period info. I can't figure out how to compare for instance today's sales against LY sales to give me the figure of how much better/worse we did copmared to LY sales on this weekDAY in the Period.

I hope this information gives insight to where I am at.
thanks in advance
Marco

 

[lespaul]

the information that is missing right now is the week/period info

does this mean you can't get this information into your table or you can't get your query to work without this information?

***************
I'm going to assume that you are trying to write some kind of report that shows sales from Monday week 5 this year compared to Monday week 5 last year. Is that right?

So you need today's date, and what day of the week it is and what week of the year it is; with that information you want to find this week one year ago and the date that is the day of the week as today.

(I'm going to use your field names, but I REALLY hope you don't have a field that is REALLY named DATE!!)

I would create a query that gets todays Week, period, and day of the week:

qryCurrentDateInfo:
SELECT DISTINCT WEEK, PERIOD, Format('dddd', Date) As DayOfWeek FROM DailySales WHERE DATE = Yesterday

Using the information from qryCurrentDateInfo we can get the previous year information:

qryPreviousDateInfo:
SELECT DISTINCT WEEK, PERIOD, Date FROM DailySales WHERE WEEK = qryCurrentDateInfo.Week and Format('dddd', Date) = qryCurrentDateINfo.DayOfWeek

Now you can use both of these queries to get the sales date for both of those dates.

HTH

leslie

 

[MarcoPerez21]

Leslie,
i made a typo the date is supposed to be day in the daily_sales tbl.
okay getting back to the qry
yes your assumption was correct, the only thing is that I am not that proficient in sql.

Marco

 

[lespaul]

If you correct the table and field names do the queries above work to get the matching period and day of week?

leslie

 

[MarcoPerez21]

okay am i supposed to create a whole new qry?
or am i supposed to enter this in the qry that generates the tbl_des1_daily_sales(this is the actual name of the table)


marco

 

[lespaul]

I would create a new query for each of them and just see if they work and if they return the information that you need to get the sales data. If they return the correct information then we can move on to creating the query that will be the source of your report.

leslie

 

[MarcoPerez21]

Leslie,
okay i have the qry that will give me the data that i need...

 

[lespaul]

So those two queries return the correct day, period, week and day of the week (especially the last year one!!)?

Now you just need to take your main query where you are trying to get the sales comparison and use the date field from the second query to get last year's information.

What is the query you are using to get the report information?

leslie

 

[MarcoPerez21]

Leslie,

I didn't use your qry i had another query that gave me all of that except the period/week data but here is the "Master qry" that I am using to feed my report.
if you can tweak this qry out to inlclude the period/week it would be great.

PARAMETERS [Enter Beginning Date] DateTime;
SELECT qry_salesinput_day1.Period, qry_salesinput_day1.Week, qry_salesinput_day1.Day, qry_salesinput_day1.[Store Am sales], qry_salesinput_day1.[Store Pm sales], qry_salesinput_day1.[Store Praline Sales], qry_salesinput_day1.[Store Merch Sales], qry_salesinput_day1.[Store Postage Sales], qry_salesinput_day1.[MailOrder Praline Sales], qry_salesinput_day1.[MailOrder Merch Sales], qry_salesinput_day1.[MailOrder Postage Sales], qry_salesinput_day1.[MailOrder Total Sales], qry_salesinput_day1.[Wholesale Praline Sales], qry_salesinput_day1.[Wholesale Merch Sales], qry_salesinput_day1.[Wholesale Postage Sales], qry_salesinput_day1.[WholeSale Total sales], qry_salesinput_day1.[Planned Store hours], qry_salesinput_day1.[Actual Store hours], qry_salesinput_day1.[Planned Store Dollars], qry_salesinput_day1.[Actual Store Dollars], qry_salesinput_day1.[Overtime Dollars], qry_salesinput_day1.[Training Dollars], qry_salesinput_day1.[Am # of Tickets], qry_salesinput_day1.[Pm # of Tickets], qry_salesinput_day1.[Peak Ticket Hour], qry_salesinput_day2.Day, qry_salesinput_day2.[Store Am sales], qry_salesinput_day2.[Store Pm sales], qry_salesinput_day2.[Store Praline Sales], qry_salesinput_day2.[Store Merch Sales], qry_salesinput_day2.[Store Postage Sales], qry_salesinput_day2.[MailOrder Praline Sales], qry_salesinput_day2.[MailOrder Merch Sales], qry_salesinput_day2.[MailOrder Postage Sales], qry_salesinput_day2.[MailOrder Total Sales], qry_salesinput_day2.[Wholesale Praline Sales], qry_salesinput_day2.[Wholesale Merch Sales], qry_salesinput_day2.[Wholesale Postage Sales], qry_salesinput_day2.[Wholesale Total Sales], qry_salesinput_day2.[Planned Store hours], qry_salesinput_day2.[Actual Store hours], qry_salesinput_day2.[Planned Store Dollars], qry_salesinput_day2.[Actual Store Dollars], qry_salesinput_day2.[Overtime Dollars], qry_salesinput_day2.[Training Dollars], qry_salesinput_day2.[Am # of Tickets], qry_salesinput_day2.[Pm # of Tickets], qry_salesinput_day2.[Peak Ticket Hour], qry_salesinput_day3.Day, qry_salesinput_day3.[Store Am sales], qry_salesinput_day3.[Store Pm sales], qry_salesinput_day3.[Store Praline Sales], qry_salesinput_day3.[Store Merch Sales], qry_salesinput_day3.[Store Postage Sales], qry_salesinput_day3.[MailOrder Praline Sales], qry_salesinput_day3.[MailOrder Merch Sales], qry_salesinput_day3.[MailOrder Postage Sales], qry_salesinput_day3.[MailOrder Total Sales], qry_salesinput_day3.[Wholesale Praline Sales], qry_salesinput_day3.[Wholesale Merch Sales], qry_salesinput_day3.[Wholesale Postage Sales], qry_salesinput_day3.[Wholesale Total Sales], qry_salesinput_day3.[Planned Store hours], qry_salesinput_day3.[Actual Store hours], qry_salesinput_day3.[Planned Store Dollars], qry_salesinput_day3.[Actual Store Dollars], qry_salesinput_day3.[Overtime Dollars], qry_salesinput_day3.[Training Dollars], qry_salesinput_day3.[Am # of Tickets], qry_salesinput_day3.[Pm # of Tickets], qry_salesinput_day3.[Peak Ticket Hour], qry_salesinput_day4.Day, qry_salesinput_day4.[Store Am sales], qry_salesinput_day4.[Store Pm sales], qry_salesinput_day4.[Store Praline Sales], qry_salesinput_day4.[Store Merch Sales], qry_salesinput_day4.[Store Postage Sales], qry_salesinput_day4.[MailOrder Praline Sales], qry_salesinput_day4.[MailOrder Merch Sales], qry_salesinput_day4.[MailOrder Postage Sales], qry_salesinput_day4.[Wholesale Praline Sales], qry_salesinput_day4.[MailOrder Total Sales], qry_salesinput_day4.[Wholesale Merch Sales], qry_salesinput_day4.[Wholesale Postage Sales], qry_salesinput_day4.[Wholesale Total Sales], qry_salesinput_day4.[Planned Store hours], qry_salesinput_day4.[Actual Store hours], qry_salesinput_day4.[Planned Store Dollars], qry_salesinput_day4.[Actual Store Dollars], qry_salesinput_day4.[Overtime Dollars], qry_salesinput_day4.[Training Dollars], qry_salesinput_day4.[Am # of Tickets], qry_salesinput_day4.[Pm # of Tickets], qry_salesinput_day4.[Peak Ticket Hour], qry_salesinput_day5.Day, qry_salesinput_day5.[Store Am sales], qry_salesinput_day5.[Store Pm sales], qry_salesinput_day5.[Store Praline Sales], qry_salesinput_day5.[Store Merch Sales], qry_salesinput_day5.[Store Postage Sales], qry_salesinput_day5.[MailOrder Praline Sales], qry_salesinput_day5.[MailOrder Merch Sales], qry_salesinput_day5.[MailOrder Postage Sales], qry_salesinput_day5.[MailOrder Total Sales], qry_salesinput_day5.[Wholesale Praline Sales], qry_salesinput_day5.[Wholesale Merch Sales], qry_salesinput_day5.[Wholesale Postage Sales], qry_salesinput_day5.[Wholesale Total Sales], qry_salesinput_day5.[Planned Store hours], qry_salesinput_day5.[Actual Store hours], qry_salesinput_day5.[Planned Store Dollars], qry_salesinput_day5.[Actual Store Dollars], qry_salesinput_day5.[Overtime Dollars], qry_salesinput_day5.[Training Dollars], qry_salesinput_day5.[Am # of Tickets], qry_salesinput_day5.[Pm # of Tickets], qry_salesinput_day5.[Peak Ticket Hour], qry_salesinput_day6.Day, qry_salesinput_day6.[Store Am sales], qry_salesinput_day6.[Store Pm sales], qry_salesinput_day6.[Store Praline Sales], qry_salesinput_day6.[Store Merch Sales], qry_salesinput_day6.[Store Postage Sales], qry_salesinput_day6.[MailOrder Praline Sales], qry_salesinput_day6.[MailOrder Merch Sales], qry_salesinput_day6.[MailOrder Postage Sales], qry_salesinput_day6.[MailOrder Total Sales], qry_salesinput_day6.[Wholesale Praline Sales], qry_salesinput_day6.[Wholesale Merch Sales], qry_salesinput_day6.[Wholesale Postage Sales], qry_salesinput_day6.[Wholesale Postage Sales], qry_salesinput_day6.[Planned Store hours], qry_salesinput_day6.[Actual Store hours], qry_salesinput_day6.[Planned Store Dollars], qry_salesinput_day6.[Actual Store Dollars], qry_salesinput_day6.[Overtime Dollars], qry_salesinput_day6.[Training Dollars], qry_salesinput_day6.[Am # of Tickets], qry_salesinput_day6.[Pm # of Tickets], qry_salesinput_day6.[Peak Ticket Hour], qry_salesinput_day7.Day, qry_salesinput_day7.[Store Am sales], qry_salesinput_day7.[Store Pm sales], qry_salesinput_day7.[Store Praline Sales], qry_salesinput_day7.[Store Merch Sales], qry_salesinput_day7.[Store Postage Sales], qry_salesinput_day7.[MailOrder Praline Sales], qry_salesinput_day7.[MailOrder Merch Sales], qry_salesinput_day7.[MailOrder Postage Sales], qry_salesinput_day7.[MailOrder Total Sales], qry_salesinput_day7.[Wholesale Praline Sales], qry_salesinput_day7.[Wholesale Merch Sales], qry_salesinput_day7.[Wholesale Postage Sales], qry_salesinput_day7.[Wholesale Total Sales], qry_salesinput_day7.[Planned Store hours], qry_salesinput_day7.[Actual Store hours], qry_salesinput_day7.[Planned Store Dollars], qry_salesinput_day7.[Actual Store Dollars], qry_salesinput_day7.[Overtime Dollars], qry_salesinput_day7.[Training Dollars], qry_salesinput_day7.[Am # of Tickets], qry_salesinput_day7.[Pm # of Tickets], qry_salesinput_day7.[Peak Ticket Hour]
FROM (((((qry_salesinput_day1 INNER JOIN qry_salesinput_day2 ON (qry_salesinput_day1.Period = qry_salesinput_day2.Period) AND (qry_salesinput_day1.Week = qry_salesinput_day2.Week)) INNER JOIN qry_salesinput_day3 ON (qry_salesinput_day2.Week = qry_salesinput_day3.Week) AND (qry_salesinput_day2.Period = qry_salesinput_day3.Period)) INNER JOIN qry_salesinput_day4 ON (qry_salesinput_day3.Week = qry_salesinput_day4.Week) AND (qry_salesinput_day3.Period = qry_salesinput_day4.Period)) INNER JOIN qry_salesinput_day5 ON (qry_salesinput_day4.Week = qry_salesinput_day5.Week) AND (qry_salesinput_day4.Period = qry_salesinput_day5.Period)) INNER JOIN qry_salesinput_day6 ON (qry_salesinput_day5.Week = qry_salesinput_day6.Week) AND (qry_salesinput_day5.Period = qry_salesinput_day6.Period)) LEFT JOIN qry_salesinput_day7 ON (qry_salesinput_day6.Week = qry_salesinput_day7.Week) AND (qry_salesinput_day6.Period = qry_salesinput_day7.Period)
WHERE (((qry_salesinput_day1.Period)=11) AND ((qry_salesinput_day1.Week)=1) AND ((qry_salesinput_day1.Day)=[Enter Beginning Date]));

 

[MarcoPerez21]

Leslie,
this is the qry that i used to get the information for the report qry. Can you help me include the period/week in qry to get that information to the report?

PARAMETERS [start date] DateTime, [End Date] DateTime;
SELECT TixHistHdr.TicketNumber, CDate(Mid$(TixHistHdr!TicketDate,5,2) & "/" & Right$([TICKETDATE],2) & "/ " & Left$([TICKETDATE],4)) AS TDate, TixHistHdr.TicketTime, TixHistHdr.Location, TixHistLin.Store, TixHistLin.Quantity, TixHistLin.Category, TixHistLin.ExtendedPrice INTO tbl_SalesHistory2
FROM TixHistHdr LEFT JOIN TixHistLin ON (TixHistHdr.Store = TixHistLin.Store) AND (TixHistHdr.TicketNumber = TixHistLin.TicketNumber)
GROUP BY TixHistHdr.TicketNumber, CDate(Mid$(TixHistHdr!TicketDate,5,2) & "/" & Right$([TICKETDATE],2) & "/ " & Left$([TICKETDATE],4)), TixHistHdr.TicketTime, TixHistHdr.Location, TixHistLin.Store, TixHistLin.Quantity, TixHistLin.Category, TixHistLin.ExtendedPrice
HAVING (((CDate(Mid$([TixHistHdr]![TicketDate],5,2) & "/" & Right$([TICKETDATE],2) & "/ " & Left$([TICKETDATE],4)))>=[START Date] And (CDate(Mid$([TixHistHdr]![TicketDate],5,2) & "/" & Right$([TICKETDATE],2) & "/ " & Left$([TICKETDATE],4)))<=[END Date]));

Thanks,
Marco

 

[lespaul]

Ok, the second query is for the report?

Shouldn't really have a HAVING clause, it's used to exclude from a GROUP BY clause. SHould be a WHERE Clause.

Ok, now this query, you are running it for a date range that the user enters? and you want to return the information for those dates and for the same day of week last year? is that right?

Leslie

 

[MarcoPerez21]

Leslie,
yes that is right....the 1st qry is the one that i am using to generate my information for the report.

the second qry is the one that i am using as a "temp" it gives me the information that I want based on what the user enters. i then take the 1st qry and generate my report. on the second qry I need for it grab the previous years information so that I can include it in my report qry.


thanks,
Marco

 

[MarcoPerez21]

Leslie,
yes that is right....the 1st qry is the one that i am using to generate my information for the report.

the second qry is the one that i am using as a "temp" it gives me the information that I want based on what the user enters. i then take the 1st qry and generate my report. on the second qry I need for it grab the previous years information so that I can include it in my report qry.


thanks,
Marco

 

[lespaul]

Ok, this is the WHERE clause from the MONSTER query:

WHERE (((qry_salesinput_day1.Period)=11) AND ((qry_salesinput_day1.Week)=1) AND ((qry_salesinput_day1.Day)=[Enter Beginning Date]));


If you modify that to use the dates from the other queries, you should be good to go. Now, you may have to:

1. Take your monster query and save it as qrycurrentdata.

2. Copy your monster query and save it as qrypastdata
change the where clause of qryPastData to:

WHERE (((qry_salesinput_day1.Period)=qryPreviousDateInfo.Period) AND ((qry_salesinput_day1.Week)=qryPreviousDateInfo.Week) AND ((qry_salesinput_day1.Day)=qryPreviousDateInfo.Day));

Then create yet another query (qryGetAllData):

SELECT * FROM qryCurrentData
UNION
SELECT * FROM qryPreviousData

to give you all your results in a single table.

Now, what I would do is set it up so that all you call is the last query : qryGetAllData

So, we have:

qryCurrentDateInfo - should be the only query with a prompt to get the date to run.

qryPreviousDateInfo - uses qryCurrentDateInfo to come up with the date we need for previous year sales

qryCurrentData - uses qryCurrentDateInfo for Week period and day information

qryPastData - uses qryPreviousDateInfo for Week period and day information

qryGetAllData is a union between the current data and the previous data.

Make sense?

HTH

Leslie

 

[MarcoPerez21]

yes it makes a ton of sense....thanks leslie

 

[MarcoPerez21]

Leslie,
how do i set it up to be run by the final query? I am not that familiar with access. I've gotten alot of the information together by reading the forums posted and applying them to the project as they fit.


thanks in advance,
Marco