Selecting sales data based on dates and duration in form

[Tlewman]

I am creating a db to track the sales made by our reps to justify additional headcount. I receive sales data from our retailer via software that downloads my file (in access format) into a specified location.
The table is called tblDailySales and contains all sales by day for the past two years. It has the following fields:
Item as Nbr
Str as Nbr
Day as Text
Qty as Nbr

I have created a form for the reps to log the actual amount sold to each store/area. The underlying table (tblEventSales) contains the following fields:
Event type as Text (promo, weather related, etc)
Item as Nbr
Str as Nbr
Start Date as Text
Duration as Nbr (how many days they will sell)
Qty sold as Nbr

Now I need to determine what the average sales from tblDailySales have been for the past four weeks based on the Start Date listed in tblEventSales. Ex: If the start date for the sale is 11/15/2004, I need to average the sales from 10/26 through 11/14.

Then, I need to determine what the total sales are in tblDailySales beginning with the Start date listed in tblEventSales through the duration (# days listed in tblEventSales). Ex: if the start date for the sale is 11/15/2004, and the duration is 10 days, what were the actual sales from tblDailySales starting on 11/15 through 11/24. (Obviously this will run once the duration is complete).

I have joined my tables on the store and item numbers. Once I get the average sales amount and the totals sold, I can figure incremental and sell thru.

How do I get this date thing to work?????

 

[Andrew Fergus]

Hi,

You asked :

How do I get this date thing to work?????

Why have you set the date variables a to text format? The reason I ask is because I can get it to work where the dates are formatted as dates but it is much harder where they are formatted as text.

Once you have them formatted as "dates" then you can use maths on them to get the averages for the duration of the promotion and the 4 weeks prior.

HTH, Andrew.

 

[Tlewman]

That is the problem....I can make it work as a date but.....the data I get from the retailer brings the date back as text format. I have no control over how their data comes to me so is there some code that will change the format of their field? This table is replaced weekly and linked into my db. I tried to create a blank table with the same fields, just changing the format of the date field to date instead of text and then a macro to append from the linked table into the blank table but get import errors.....Thx!

 

[mdmilner]

This sounds related to my xls Import question yesterday.
When you say the retailer makes the data available in 'access format' what do you mean exactly? What is the extension on the file?

Append query error messages usually means the so called 'date formatted data' isn't really 100% formatted the way it needs to be. Assuming they're actually giving you a mdb file, that might explain why they didn't make it a date format. They had a problem and passed it along to you instead of trying to fix it.

Lastly, if there are errors in the field (to appear like dates) the methods Andrew hints at may not work (easily). You might find you're having to anticipate a variety of screw-ups in field formatting that require a lot of conditions to fix.

Two questions:

Do your append query again and share some of the error messages - particularly what a field looks like that works and doesn't work in the append. (try using a parameter to limit the append)

Second, do you have the option of going back to the retailer and demanding they give you the data correctly (under the assumption my guess is correct)?

Mike

 

[Tlewman]

Going back to the retailer- definitely not an option. However, something sparked my brain while I was reading this site.

It looks like if I create a query based on my linked table and use the format option (Format([TableName]![DailySalesField],"mm/dd/yyyy")) to change the text to a date, it will work. Then I can go back to the table I created and change them to date format as well.

Then I can combine my table and the retailers data (from the query) and make it work........(I'm optimistic!).
If not, you'll hear from me again.........
Thanks!

 

[Norie]

You could also goto the Design view of the table and change the data type of the date field to Date.

If all the records have valid dates in the datr field this should change them to dates.

 

[Tlewman]

OK....I finally got my tables joined but have another snag.

In a query, I now have a field named StartDate(which is the date a promo will start), a field named EndDate (I used DateAdd to take my start date and add the number of days the promo will run), a field named DailySalesDate (the individual dates from the retailer- in date format (finally) and a field named Sales (the amount sold that particular day).

What I am trying to do now is get the DailySalesDate column to only select dates between my start and end dates. I have used between [StartDate] and [EndDate] but it's not working. It will select records starting with my start date but doesn't stop at my end date. I have tried the same formula in a second query (thinking that maybe it wasn't reading EndDate because it is the name of the DateAdd formula in the original query but it didn't work there either.

I guess I am just having a mental block today. Thanks to all for your help!

 

[Andrew Fergus]

Hi

Rather than this :

I have used between [StartDate] and [EndDate] but it's not working. It will select records starting with my start date but doesn't stop at my end date

have you tried this:

>=[StartDate] and <=[EndDate]

Does that make any difference?

Andrew.

 

[Tlewman]

Yes...I have tried that as well.....It still pulls back dates beyond my end date.

 

[Andrew Fergus]

Are the formats for the end date and start date the same? Have you checked that the end date has values? If the end date is a function of the start date plus a value, have you considered using the start date plus that value instead of the end date in your query?

HTH, Andrew.