Funny iteration on days of the month

[nowanda]

Howdy!

Funny question - If I have sales data for every day of the month - and I would like to see how many days are between sales that are greater than 100$ - how would I set this up?

Here's the table in question:
Month___________day_in_month________
_______1___2___3___4___5...___30___31
---------------------------------------------
July___75__15_115__50_125___10___67
Aug___175_35__15__50_105___38___73

So in July I had an interval of 2, 1, ... etc
And in Aug I had an interval of 0, 3, ... etc

I figure I'll need a module that goes through each field [1],[2],..[31] and checks if the value is >100. If it's >100 I'll need to compare it to the next value.. and so on..

Possible? Probable?

Thanks!

Nowanda

 

[Russell Hauf]

You can probably do this in a query. Can I bother you to give a better example of your table? I'm pretty confident that we can get this solved if you can do this. (Even if you just copy a few rows of the table then paste here it will be fine for me).

Thank you,

Russell

 

[nowanda]

Hooray - Russell to the Rescue ray: !

Here's a more clear copy (thankyou Colo).. but this little lamb requires a shephard's guidance to get a query that isn't >256 characters long!!

Note that this display only includes up to the 21st. - It actually has data up to the 31st. Some of the entries are null - they can be assumed to be zero.

Thanks a zillion!

Nowanda

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	ID	month	year	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21
2	1	Apr	2002	0	0	0	94		0	0	0	178	46	50	49	0	46	130	0	0	0	0	160	0
3	2	May	2002	0	0	0	0	0	23	30		50	50	0	150	36	48	50	0	0	178	0	0	0
4	3	Jun	2002		15	80	50	0	0	0	84	64			0	0	0	43	0	0	120	100	150	31
5	4	Jul	2002	32	64	0	0	180	0	0	26	0	0	18	142	60	0	222	0	104	0	26	0	44
6	5	Aug	2002	24	26	180	0	0	86	100	28	0	0	80	116	0	26	46	0	0		200	0	0
Sheet1

 

[Russell Hauf]

Man, that is one ugly table! Do you (or does someone) enter that data by hand? Just FYI, it would be much more manageable if you just entered the date and the sales amount. Then you could manipulate it in several different ways (including into a table that looks like yours). Unfortunately, the reverse is not true. I am a little busy today, so give me a while to think about this. Maybe someone else will step in before I get a chance to finish.

Sorry,

Russell

p.s. Why are you worried about 256 characters?

 

[Russell Hauf]

Also, is this table currently in Access or Excel?

 

[nowanda]

Hmm...

Yup - it was all entered by hand I believe (I feel sorry for the chap that had to do that). It's possible to export it to excel without any trouble if handling it would be easier in excel.

The table is in Access 2000 - An idea I had was to create a query whose results were "binary" by letting each day =1 if the sales were greater than 100 and if not then letting the value =0. Also thought of concatenating the results all together - like so:

month year concat_result
Jan 1992 0001100000000100000000100000000
Jan 1981 0000000000000000000000000000000
Jan 1982 0000000000000000000000000000000
Jan 1973 0000000000000000000001000000000

And then maybe some sort of Do..While.. going through the concat_result one character at a time.

I suppose if the table wasn't this ugly then I wouldn't be in desperate need of Mr. Excel's message board!!



Thanks!

Nowanda

 

[Parra]

What about creating a cross tab query or exporting the data to Excel and creating a Pivot Table. Wait you can also create Pivot Tables in Access.

Parra

 

[Russell Hauf]

Well, here's what I would do. I would export the data into Excel and transform the table. Something like what I have below. Before you look below, let me tell you what I did (because the table below is not exactly like what I have - I've only included some example rows and columns). First, I put the earliest "date" that is in your table in a cell below the table. Then I dragged down to fill in all dates between that one and the last "date" (I added Jan 2003 just to make sure everything worked right even if there were skipped months, etc. I also didn't think that the ID was necessary, but I included it just in case). Then I applied the formulas included below.

Book3
	A	B	C	D	E	F	G
1	ID	month	year	1	2	3	4
2	1	Apr	2002	0	0	0	94
3	2	May	2002	0	0	0	0
4	3	Jun	2002		15	80	50
5	4	Jul	2002	32	64	0	0
6	5	Aug	2002	24	26	180	0
7	6	Jan	2003	35	23	83	81
8
9	fldDate	fldSales	fldID
10	04/01/2002	0	1
11	04/02/2002	0	1
12	04/03/2002	0	1
13	04/04/2002	94	1
14	05/01/2002	0	2
15	06/02/2002	15	3
16	06/03/2002	80	3
17	06/04/2002	50	3
18	07/01/2002	32	4
19	07/02/2002	64	4
20	08/01/2002	24	5
21	08/02/2002	26	5
22	08/03/2002	180	5
23	01/01/2003	35	6
24	01/02/2003	23	6
ConvertData

Then I just copied the new table, went to Access, selected the Tables tab of the Database Window, and went to Edit-Paste. I clicked yes when it asked me if my data contained header rows. I then renamed my table to tbl081303.

Ok, then I wrote a query like I have below. Note that you can modify this to suit your needs:

• SELECT X.fldDate, X.fldSales, [fldDate]-(SELECT Max(fldDate) FROM tbl081303
  WHERE tbl081303.fldSales>=100 AND tbl081303.fldDate< X.fldDate) AS DaysBetween
  FROM tbl081303 AS X
  WHERE X.fldSales>=100;

Just change tbl081103 to your table name, and the field names to your field names (if you used ones different from my Excel example). Don't change X.

This worked for me, so let me know if you have any problems executing it and we'll work it out.

HTH,

Russell

p.s. Good luck on that Pivot Table, Parra!

 

[nowanda]

sweet holy mother of pearl!!

It actually works! I'll have to do some extra legwork because I have reams of data - but this is a good platform to jump off of!

Thanks Sensei Russell - - I guess my firsborn will need to be named R.H.!

Nowanda