Date Confusion

[Charles D Clayton Jr]

I am having problems figuring out a query. I have some widgets that
need to go through 4 categories of testing. Each widget is uniquely
identified. When they go through the testing, I enter the date. My
boss wants to see our progress with goals. I went in and for each
widget gave it a target date. He wants to see our progress by weeks.
I just cannot seem to get anything to work. What he wants is
something like this: (A = Actual (widgets done that week) G = Goals
for that week)

Test 1 Test 2 Test 3 Test 4
A G A G A G A G
10/5/2003 5 10 4 3 0 0 0 0
10/12/03 15 8 10 10 0 0 0 0
10/19/2003 25 20 15 10 10 10 0 0

so this is showing that I for the week of 10/5/03 I completed 5
widgets through test #1 but I had a goal of 10. For test 1 I exceeded
my goal by 1 and I had not goals for that week for the remaining two
tests.
I am drawing a mental blank here. Does anybody have any good
suggestions?

Thanks,

Charles D Clayton Jr

 

[Van T. Dinh]

Post relevant details of your Tables.

Where do you get the Goals figures from?

Post any SQL String you got to date.

 

[Charles D Clayton Jr]

I have one table and it has the widget name, the names of the 4 tests
and the names goals of the 4 tests and an autonumber ID. So we are
looking at 10 columns. At present, we have a date in each of the 4
goals columns. This date is somewhat arbitrary. My boss looked at
all the widgets and inputed what he thinks are viable goals for each
widget to pass it test. Each test is performed by one of our craft
people and then I input the date they performed the test. At the end
of a week he wants me to print out a report that shows our progress
with grand totals and a breakdown by week of our actual work rate
verses are goal rate.

Before he wanted to know just our actual rate. I had to group the
widgets by locations (7) verses material compositions and size ranges.
The only way I could get it to look right (like a spreadsheet) was to
create a function to give an ID based on its size/location & specs.
Then create another function that would take those numbers and group
the widgets into 6 categories and then make a crosstab query that
would retrieve multiple values (since each widget would eventually go
through each test) as well as the total. The SQL looks like this:

TRANSFORM "~" & Count([racked]) & "~" & Count([fit]) & "~" &
Count([Weld]) & "~" & Count([QC]) & "~" & Count([f Loc]) AS stage
SELECT Psort(fPID([f loc],[size],[spec])) AS test, Count(Spools.Spool)
AS Total
FROM Spools
GROUP BY Psort(fPID([f loc],[size],[spec]))
PIVOT Spools.[F Loc] In ("BPD","APD","BDD","ADD","BCD","ACD","ICP");

fPID is the first function that assigns a number based on its
location/size and spec
Psort is the second function which groups into on of 6 categories
The tests are racked/fit/weld and QC
f Loc is the location
The table name is Spool
The location possibilities are: BPD/APD/BDD/ADD/BCD/ACD/ICP

In the report, I would separate out the values and format it nicely.

Now this new report has me stumped. I thought I would do something
similar. SO I created a different function to to put them into new
groups (because only some of the tests does he want to see the data
from) which would be the rows, then I used the multiple values from
the dates as the values but I cannot figure out a way to get the dates
as the column headings. Since each widget would have up to 8 dates, I
do not know how to get a generic date range. I made a query to
reformat my dates as a week number (and then add the year to it so
that it would sort correctly). This is what it looks like:
DateRange: (DatePart("yyyy",[rack by])) & "~" & (DatePart("ww",[rack
by])). But the problem is that I picked the "[rack by]" and when I
add that to the crosstab for the column headings it skews the data.
Maybe there is a way to get the "DateRange" from something other than
one test.

What is the really frustrating part of all this is that I thought I
had the problem solved until I looked at the data and saw that it was
wrong and I figures it was the "DateRange" that was making it wrong.
But by that I time I had worked so hard on it that I just could not
think of any solution.

Maybe you will have a suggestion or two. If not, I do thank you for
taking the time from your busy day to at least consider my dilemna

Blessings,

Charles D Clayton Jr

 

[Charles D Clayton Jr]

I have found a solution that works but I do not know if it is very
good. It is easy to make the query to calculate the totals for each
widget group by a week date but I could never make a query that would
work on more than one test at the same time. So I made a query for
each test, then I export it to a Excel Workbook where each query is
its own sheet. I use vlookup to combine all the tabs onto one sheet
organized by dates. I import that information back into Access in the
form of a temporary table and then run a report based on that
information.

All this work makes me wish that Union queries were more flexible. It
would be nice if instead of adding the second query vertically, it
would do it horizontally. What I mean is that my first query has two
columns and my second query has two columns and then combine them so
that I have four columns (and this is exactly what I had to do in
Excel).

Hoping everyone has a safe holiday,

Charles D Clayton Jr

I have one table and it has the widget name, the names of the 4 tests
and the names goals of the 4 tests and an autonumber ID. So we are
looking at 10 columns. At present, we have a date in each of the 4
goals columns. This date is somewhat arbitrary. My boss looked at
all the widgets and inputed what he thinks are viable goals for each
widget to pass it test. Each test is performed by one of our craft
people and then I input the date they performed the test. At the end
of a week he wants me to print out a report that shows our progress
with grand totals and a breakdown by week of our actual work rate
verses are goal rate.

Before he wanted to know just our actual rate. I had to group the
widgets by locations (7) verses material compositions and size ranges.
The only way I could get it to look right (like a spreadsheet) was to
create a function to give an ID based on its size/location & specs.
Then create another function that would take those numbers and group
the widgets into 6 categories and then make a crosstab query that
would retrieve multiple values (since each widget would eventually go
through each test) as well as the total. The SQL looks like this:

TRANSFORM "~" & Count([racked]) & "~" & Count([fit]) & "~" &
Count([Weld]) & "~" & Count([QC]) & "~" & Count([f Loc]) AS stage
SELECT Psort(fPID([f loc],[size],[spec])) AS test, Count(Spools.Spool)
AS Total
FROM Spools
GROUP BY Psort(fPID([f loc],[size],[spec]))
PIVOT Spools.[F Loc] In ("BPD","APD","BDD","ADD","BCD","ACD","ICP");

fPID is the first function that assigns a number based on its
location/size and spec
Psort is the second function which groups into on of 6 categories
The tests are racked/fit/weld and QC
f Loc is the location
The table name is Spool
The location possibilities are: BPD/APD/BDD/ADD/BCD/ACD/ICP

In the report, I would separate out the values and format it nicely.

Now this new report has me stumped. I thought I would do something
similar. SO I created a different function to to put them into new
groups (because only some of the tests does he want to see the data
from) which would be the rows, then I used the multiple values from
the dates as the values but I cannot figure out a way to get the dates
as the column headings. Since each widget would have up to 8 dates, I
do not know how to get a generic date range. I made a query to
reformat my dates as a week number (and then add the year to it so
that it would sort correctly). This is what it looks like:
DateRange: (DatePart("yyyy",[rack by])) & "~" & (DatePart("ww",[rack
by])). But the problem is that I picked the "[rack by]" and when I
add that to the crosstab for the column headings it skews the data.
Maybe there is a way to get the "DateRange" from something other than
one test.

What is the really frustrating part of all this is that I thought I
had the problem solved until I looked at the data and saw that it was
wrong and I figures it was the "DateRange" that was making it wrong.
But by that I time I had worked so hard on it that I just could not
think of any solution.

Maybe you will have a suggestion or two. If not, I do thank you for
taking the time from your busy day to at least consider my dilemna

Blessings,

Charles D Clayton Jr

Post relevant details of your Tables.

Where do you get the Goals figures from?

Post any SQL String you got to date.