How to do this in VBA (or is there an easier way)

[ste4en]

Data in my table includes the folliwing information - the sales by week are cummulative ( I cant change the way that that happens)

Type, week1, week2, week3, week4
cars, 1, 3, 5,
bikes, 1, 2,
trikes, 0, 1, 2, 5,

I need to be able to get the data for any week as examples below.

Sales at week 4,
Cars, 5
Bikes, 2
Trikes, 5


Sales at week 3,
Cars, 5
Bikes, 2
Trikes, 2

etc

This is of course an example of the data structure but how can I achieve the results a query or some VBA.?

Thanks

Steve

 

[abaldwin]

I think this can be done with some looping.

Basically
Get All the fields returned in a record set

Get the current week number or week number you want to report into a variable.

Starting at the beginning of the record set and begin looping
For each record
retrun the field name "week" & Weeknumber variable
If this field is null then
take weeknumber variable and -1 and check again.

When you get the number you want write it the part number and value to a temp table or text file.

You get this type of code up and then I am sure some of us could help you trouble shoot it.

Also, does this table get cleared out every year?




Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[fneily]

If that's your table structure (Type, week1, week2, week3, week4), my first question is would you have a week5, week6, etc.?? Then your table is not normalized (repeating column headings) which leads to variable length records, which you have, which goes against the first normal form.
Since your data is cummulative, can't you just propagate the data to the last column, eg. for bikes, just copy 2 into weeks 3 and 4. So bikes would look like 1,2,2,2 (you're adding 0). Then you can just pick off any week's data you want.

 

[abaldwin]

fneily,

I would have to agree with you on the normalization (as I normally hammer ops on this) but he stated in the original post that he has no controll over the data inbound.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[ste4en]

The example is not is not quite the table structure I am dealing with but the relevant table will contain a null value for the update date (WEEK#) if there were no sales that week i.e. it does not add this weeks qty of zero to last weeks qty, which would make my life easier. I would have the total quantity for every week (or any date that I query for)

abaldwin thanks for your guide, I am trying to write some code...

 

[abaldwin]

I had to do the same thing parsing reports from a home office so we could all managers at local site to see their data. Sorry I do not have the code at my finger tips but the outline I gave you will work. Especially if hte field names are numerical.


Once you get some code posted I am sure you will receive help from myself and others to troubleshoot any problems.


I HATE NON-NORMALIZED data. Always thought the "super duper GODS at the home office" were off their rockers.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[TheAceMan1]

How are ya ste4en . . .

[blue]Data in my table includes the folliwing information . . .[/blue]

Not trying to rain o your parade but:

The table you describe is in a spreadsheet format! . . . not gonna work very well if you intend to push on. I was going to suggest you look at [blue]CrossTab[/blue] queries, but your headed for really big problems down the line in dealing with this table . . .

Note: [blue]A spreadsheet is not a database and a database is not a spreadsheet[/blue] and trying to make one out of the other can become very complicated! (I can't help but suspect that the source of the table was in fact a spreadsheet!)

The current table you prescribe to us is not suitable for DB manipulation . . . that is . . . you may solve this issue, but I'll bet after that next to nothing will be accomplished . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[ste4en]

TheAceMan1, Thanks for your reply. I presented my data in a tabular form to describe what I am dealing with and it seems to have been less clear. I uploaded a small database which includes the table of data I am dealing with. I have so far not been able to create code as suggested by abaldwin.

Data is added to the table either weekly, daily or randomly. In the update progress the database creates a full set records for that date - one for every "Step" at that point all steps have zero progress for that date. A cummulative progress amount is entered to the Steps that have progress, the ones that are not progressed stay as zero.

Now I could just take the maximum progress number from each Step however progress sometimes goes down. e.g. they record that they installed 100 this week taking the total installed to say 500. However next week the realised they overstated and may bring that down to 400 example in the data 8297-FORMS - on 10/4 it was reported as 4431, however on 10/15 it was reported as 3200.

So for any cut off date for every step I need to know the latest value that is not zero. I need the cut off dates so that I can report period data. In the example above for period 10/4 - 10/15 the period quantity would be -1,231.

http://www.hardyus.com/db1.zip

Any ideas, Thanks

 

[TheAceMan1]

Have a look at the following links so you can see what we see and better understand.

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

See Ya! . . . . . .

 

[ste4en]

TheAceMan1; I appreciate the links, but I am not really in control of this data as I suggested in my original post. I am linking to a table in a sub-contractors database to get what I need.

 

[TheAceMan1]

Roger That ste4en! . . .

[blue]I need to be able to get the data for any week as examples below.[/blue]

Just construct an [blue]select query[/blue] to return the particular column and use that . . .

See Ya! . . . . . .

 

[TheAceMan1]

ste4en . . .

Scratch my last post (hit submit too soon), you can't do it that way either. I also notice there's a particular chronilogical order to the table ([blue]besides violating 1st normal form[/blue] in table structure!) . . .

I'm at a loss here . . . nothing fits! . . .

See Ya! . . . . . .

 

[ste4en]

I think Abaldwin, has the right approach, but my first explaination was not accurate. I need to

For each Step e.g. 8297-BACKFILL; find the latest or maximum date and return the value of Progress for each step.

But i dont know how..

 

[PHV]

You may try the following 3 steps procedure:
Create a composite index on tbldata(Step,Date)

Create a query named, say, qryProgress:
SELECT Step, [Date], Progress
FROM tbldata
WHERE Progress<>0;

Now your query:
SELECT A.Step, A.Date, A.Progress, B.Date AS LastDate, A.Progress-Nz(B.Progress,0) AS Quantity
FROM tbldata AS A LEFT JOIN qryProgress AS B ON (A.Date > B.Date) AND (A.Step = B.Step)
WHERE B.Date Is Null
OR B.Date = (SELECT Max([Date]) FROM tbldata WHERE Step=A.Step AND [Date]<A.Date AND Progress<>0)
ORDER BY 1, 2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886