pigeon hole report

[Guest]

Using Access 2000 database project with Sql Server 2000 Back end.
Issue:
A drafting firm has 60 boxes divided into 36 - 2" squares - 6 across and 6
down for storing architectural / engineering plans. The Access Project
tracks the Customer Name, Job Number, Plan Name and Box Location of these
plan sets (among other info). I have built a report that uses these 4 fields
formatted in 2" squares printed on 17x22 paper cut to fit in the doors of the
cabinets. Works great as long as there is a 1 to 1 ratio of jobs to box
locations. Now there are multiple elevations and job numbers for a single
box location. The report now generates several "labels of boxes" in the same
box-location. (ie: A-001, A-001,A-001, A-001, A-002, A-003, A-003, A-004,
etc..
Question 1:
Is there a way to create a report that consolidates all of the same box
locations into one "box label area" on the report (using the first
instances' Customer
Name, Plan name, etc? Tried Using Group by, Select Distinct, select top 1.
None worked.
Question 2:
Is there a way to create a similar/same report that will show the "empty"
boxes even though there is no info for that box location and the box location
itself is not used in the database? Example: boxes A-001 thru A-036 have
been filled and four of these plan sets have been deleted/archived. The Box
Location in the Database has been changed to DF ( dead File) from thier
orignal: A-011, A-015, A-024 and A-028. The current report skips these
numbers, putting A-012 where A-013 should be, moving the rest of the numbers
accordingly.
Question 3:
Related to #2. Can a Report be generated that will "auto-generate"
boxlocations (without any other info) between a set of parameteres (ie: B-001
thru B-036), that can be used as a blank slate to hand write info on until
the box gets full and report #1 can be generated?

 

[Duane Hookom]

Assuming tblJobPlans with field names CustName, JobNum, PlanName, and
BoxLocation and sample data as you suggested...
You should be able to create a crosstab query that makes a grid with all the
plan information for a specific pigeon concatenated together. The generic
concatenate function comes from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. The SQL
of your crosstab would be:

TRANSFORM First(Concatenate("Select CustName & ' ' & JobNum & ' ' & PlanName
FROM tblJobPlans WHERE BoxLocation ='" & [boxLocation] & "'",Chr(13) &
Chr(10))) AS Expr3
SELECT Left([BoxLocation],1) AS BoxNum, (Val(Mid([BoxLocation],3))-1)\6 AS
Expr1
FROM tblJobPlans
GROUP BY Left([BoxLocation],1), (Val(Mid([BoxLocation],3))-1)\6
PIVOT (Val(Mid([BoxLocation],3))-1) Mod 6+1 In (1,2,3,4,5,6);

You should be able to create a report with the exact contents you want
including the blank pigeon holes.

 

[Guest]

Duane,
Thanks for the info. I am using an ACCESS adp file (front end for a SQL
2000 backend), which appearently does not have all the things in a simple
Access Database, expressly Crosstab Queries. When trying to create a report
Query (I only seem to have one type of query in this set up) a couple things
are missing, "Transform" function, "First" function and "mid" and "val"
funtions and. I downloaded your Concatenate function and can get that part
to run to concatenate some items using VB code modules, but the rest is lost
to me.
I imported the table and reports into a stand alone ACCESS DB and found the
crosstab query, pursued that for a bit, then discarded it as I cannot get
that track to work in the "live" set up.
If you have any guides as to the Project/sql 2000 client /server thing, I
would be most appreciative.

Thanks,
bob

Assuming tblJobPlans with field names CustName, JobNum, PlanName, and
BoxLocation and sample data as you suggested...
You should be able to create a crosstab query that makes a grid with all the
plan information for a specific pigeon concatenated together. The generic
concatenate function comes from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. The SQL
of your crosstab would be:

TRANSFORM First(Concatenate("Select CustName & ' ' & JobNum & ' ' & PlanName
FROM tblJobPlans WHERE BoxLocation ='" & [boxLocation] & "'",Chr(13) &
Chr(10))) AS Expr3
SELECT Left([BoxLocation],1) AS BoxNum, (Val(Mid([BoxLocation],3))-1)\6 AS
Expr1
FROM tblJobPlans
GROUP BY Left([BoxLocation],1), (Val(Mid([BoxLocation],3))-1)\6
PIVOT (Val(Mid([BoxLocation],3))-1) Mod 6+1 In (1,2,3,4,5,6);

You should be able to create a report with the exact contents you want
including the blank pigeon holes.
--
Duane Hookom
MS Access MVP
--

Using Access 2000 database project with Sql Server 2000 Back end.
Issue:
A drafting firm has 60 boxes divided into 36 - 2" squares - 6 across and 6
down for storing architectural / engineering plans. The Access Project
tracks the Customer Name, Job Number, Plan Name and Box Location of these
plan sets (among other info). I have built a report that uses these 4 fields
formatted in 2" squares printed on 17x22 paper cut to fit in the doors of the
cabinets. Works great as long as there is a 1 to 1 ratio of jobs to box
locations. Now there are multiple elevations and job numbers for a single
box location. The report now generates several "labels of boxes" in the same
box-location. (ie: A-001, A-001,A-001, A-001, A-002, A-003, A-003, A-004,
etc..
Question 1:
Is there a way to create a report that consolidates all of the same box
locations into one "box label area" on the report (using the first
instances' Customer
Name, Plan name, etc? Tried Using Group by, Select Distinct, select top 1.
None worked.
Question 2:
Is there a way to create a similar/same report that will show the "empty"
boxes even though there is no info for that box location and the box location
itself is not used in the database? Example: boxes A-001 thru A-036 have
been filled and four of these plan sets have been deleted/archived. The Box
Location in the Database has been changed to DF ( dead File) from thier
orignal: A-011, A-015, A-024 and A-028. The current report skips these
numbers, putting A-012 where A-013 should be, moving the rest of the numbers
accordingly.
Question 3:
Related to #2. Can a Report be generated that will "auto-generate"
boxlocations (without any other info) between a set of parameteres (ie: B-001
thru B-036), that can be used as a blank slate to hand write info on until
the box gets full and report #1 can be generated?

 

[Duane Hookom]

There are some crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. I don't know if these
will help. There are also some reports in the calendar reports and corp tech
demos that have layouts determined by code and values stored in the records.

--
Duane Hookom
MS Access MVP

Duane,
Thanks for the info. I am using an ACCESS adp file (front end for a SQL
2000 backend), which appearently does not have all the things in a simple
Access Database, expressly Crosstab Queries. When trying to create a report
Query (I only seem to have one type of query in this set up) a couple things
are missing, "Transform" function, "First" function and "mid" and "val"
funtions and. I downloaded your Concatenate function and can get that part
to run to concatenate some items using VB code modules, but the rest is lost
to me.
I imported the table and reports into a stand alone ACCESS DB and found the
crosstab query, pursued that for a bit, then discarded it as I cannot get
that track to work in the "live" set up.
If you have any guides as to the Project/sql 2000 client /server thing, I
would be most appreciative.

Thanks,
bob

Assuming tblJobPlans with field names CustName, JobNum, PlanName, and
BoxLocation and sample data as you suggested...
You should be able to create a crosstab query that makes a grid with all the
plan information for a specific pigeon concatenated together. The generic
concatenate function comes from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. The SQL
of your crosstab would be:

TRANSFORM First(Concatenate("Select CustName & ' ' & JobNum & ' ' & PlanName
FROM tblJobPlans WHERE BoxLocation ='" & [boxLocation] & "'",Chr(13) &
Chr(10))) AS Expr3
SELECT Left([BoxLocation],1) AS BoxNum, (Val(Mid([BoxLocation],3))-1)\6 AS
Expr1
FROM tblJobPlans
GROUP BY Left([BoxLocation],1), (Val(Mid([BoxLocation],3))-1)\6
PIVOT (Val(Mid([BoxLocation],3))-1) Mod 6+1 In (1,2,3,4,5,6);

You should be able to create a report with the exact contents you want
including the blank pigeon holes.
--
Duane Hookom
MS Access MVP
--

Using Access 2000 database project with Sql Server 2000 Back end.
Issue:
A drafting firm has 60 boxes divided into 36 - 2" squares - 6 across and 6
down for storing architectural / engineering plans. The Access Project
tracks the Customer Name, Job Number, Plan Name and Box Location of these
plan sets (among other info). I have built a report that uses these 4 fields
formatted in 2" squares printed on 17x22 paper cut to fit in the doors

of
the

cabinets. Works great as long as there is a 1 to 1 ratio of jobs to box
locations. Now there are multiple elevations and job numbers for a single
box location. The report now generates several "labels of boxes" in

the
same

box-location. (ie: A-001, A-001,A-001, A-001, A-002, A-003, A-003, A-004,
etc..
Question 1:
Is there a way to create a report that consolidates all of the same box
locations into one "box label area" on the report (using the first
instances' Customer
Name, Plan name, etc? Tried Using Group by, Select Distinct, select

top
1.

None worked.
Question 2:
Is there a way to create a similar/same report that will show the "empty"
boxes even though there is no info for that box location and the box location
itself is not used in the database? Example: boxes A-001 thru A-036 have
been filled and four of these plan sets have been deleted/archived.

The
Box

Location in the Database has been changed to DF ( dead File) from thier
orignal: A-011, A-015, A-024 and A-028. The current report skips these
numbers, putting A-012 where A-013 should be, moving the rest of the numbers
accordingly.
Question 3:
Related to #2. Can a Report be generated that will "auto-generate"
boxlocations (without any other info) between a set of parameteres

(ie:
B-001

thru B-036), that can be used as a blank slate to hand write info on until
the box gets full and report #1 can be generated?

 

[Guest]

Duane,
Thanks for the help! I presented the problem to the sqlserver.programmers
group as I found a similar thread dealing with max() function to return only
one of the many boxlocation records. After a bit of learning on my part, and
help from Aarron, and Steve Kass, I was able to develop the following Stored
procedure, which works Perfectly for the report I was trying to generate. I
had to generate a two tables which when joined creates all the letters and
numbers then left outer join-ed with the that information view and where ever
there are nulls, the procedure creates the blanks that show up on the report.
This is I think a cross tab query,in effect, just a different way to do it
since there is the sql programming avalaible to the .adp Project.
Thanks again for your help!
bob

Procedure spMultiPlanBoxes2

@FirstBox varchar (8),
@LastBox varchar (8)
As

select
b.box as box,
case when CName is null then '' else CName end as CName,
case when JNum is null then '' else JNum end as JNum,
case when PName is null then '' else PName end as PName,
case when Elev is null then '' else Elev end as Elev

from (select fldPlanBank + '-' + num as Box
from tblThirtySix cross join tblPlanBank) B
left outer join
(SELECT m.Box, m.cName, m.jNum, m.pName, m.Elev, m.ID
FROM vwMultiPlanBin M INNER JOIN
(SELECT id = Min(id), box
FROM vwMultiPlanBin
GROUP BY box) i ON m.id = i.id) A
on b.box = a.box
WHERE b.Box between @FirstBox and @LastBox
ORDER BY A.Box
/* set nocount on */
return

There are some crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. I don't know if these
will help. There are also some reports in the calendar reports and corp tech
demos that have layouts determined by code and values stored in the records.

--
Duane Hookom
MS Access MVP

Duane,
Thanks for the info. I am using an ACCESS adp file (front end for a SQL
2000 backend), which appearently does not have all the things in a simple
Access Database, expressly Crosstab Queries. When trying to create a report
Query (I only seem to have one type of query in this set up) a couple things
are missing, "Transform" function, "First" function and "mid" and "val"
funtions and. I downloaded your Concatenate function and can get that part
to run to concatenate some items using VB code modules, but the rest is lost
to me.
I imported the table and reports into a stand alone ACCESS DB and found the
crosstab query, pursued that for a bit, then discarded it as I cannot get
that track to work in the "live" set up.
If you have any guides as to the Project/sql 2000 client /server thing, I
would be most appreciative.

Thanks,
bob

Assuming tblJobPlans with field names CustName, JobNum, PlanName, and
BoxLocation and sample data as you suggested...
You should be able to create a crosstab query that makes a grid with all the
plan information for a specific pigeon concatenated together. The generic
concatenate function comes from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. The SQL
of your crosstab would be:

TRANSFORM First(Concatenate("Select CustName & ' ' & JobNum & ' ' & PlanName
FROM tblJobPlans WHERE BoxLocation ='" & [boxLocation] & "'",Chr(13) &
Chr(10))) AS Expr3
SELECT Left([BoxLocation],1) AS BoxNum, (Val(Mid([BoxLocation],3))-1)\6 AS
Expr1
FROM tblJobPlans
GROUP BY Left([BoxLocation],1), (Val(Mid([BoxLocation],3))-1)\6
PIVOT (Val(Mid([BoxLocation],3))-1) Mod 6+1 In (1,2,3,4,5,6);

You should be able to create a report with the exact contents you want
including the blank pigeon holes.
--
Duane Hookom
MS Access MVP
--

Using Access 2000 database project with Sql Server 2000 Back end.
Issue:
A drafting firm has 60 boxes divided into 36 - 2" squares - 6 across and 6
down for storing architectural / engineering plans. The Access Project
tracks the Customer Name, Job Number, Plan Name and Box Location of these
plan sets (among other info). I have built a report that uses these 4
fields
formatted in 2" squares printed on 17x22 paper cut to fit in the doors of
the
cabinets. Works great as long as there is a 1 to 1 ratio of jobs to box
locations. Now there are multiple elevations and job numbers for a single
box location. The report now generates several "labels of boxes" in the
same
box-location. (ie: A-001, A-001,A-001, A-001, A-002, A-003, A-003, A-004,
etc..
Question 1:
Is there a way to create a report that consolidates all of the same box
locations into one "box label area" on the report (using the first
instances' Customer
Name, Plan name, etc? Tried Using Group by, Select Distinct, select top
1.
None worked.
Question 2:
Is there a way to create a similar/same report that will show the "empty"
boxes even though there is no info for that box location and the box
location
itself is not used in the database? Example: boxes A-001 thru A-036 have
been filled and four of these plan sets have been deleted/archived. The
Box
Location in the Database has been changed to DF ( dead File) from thier
orignal: A-011, A-015, A-024 and A-028. The current report skips these
numbers, putting A-012 where A-013 should be, moving the rest of the
numbers
accordingly.
Question 3:
Related to #2. Can a Report be generated that will "auto-generate"
boxlocations (without any other info) between a set of parameteres (ie:
B-001
thru B-036), that can be used as a blank slate to hand write info on until
the box gets full and report #1 can be generated?

 

[Duane Hookom]

Thanks for replying back with your solution.

--
Duane Hookom
MS Access MVP
--

Duane,
Thanks for the help! I presented the problem to the sqlserver.programmers
group as I found a similar thread dealing with max() function to return only
one of the many boxlocation records. After a bit of learning on my part, and
help from Aarron, and Steve Kass, I was able to develop the following Stored
procedure, which works Perfectly for the report I was trying to generate. I
had to generate a two tables which when joined creates all the letters and
numbers then left outer join-ed with the that information view and where ever
there are nulls, the procedure creates the blanks that show up on the report.
This is I think a cross tab query,in effect, just a different way to do it
since there is the sql programming avalaible to the .adp Project.
Thanks again for your help!
bob

Procedure spMultiPlanBoxes2

@FirstBox varchar (8),
@LastBox varchar (8)
As

select
b.box as box,
case when CName is null then '' else CName end as CName,
case when JNum is null then '' else JNum end as JNum,
case when PName is null then '' else PName end as PName,
case when Elev is null then '' else Elev end as Elev

from (select fldPlanBank + '-' + num as Box
from tblThirtySix cross join tblPlanBank) B
left outer join
(SELECT m.Box, m.cName, m.jNum, m.pName, m.Elev, m.ID
FROM vwMultiPlanBin M INNER JOIN
(SELECT id = Min(id), box
FROM vwMultiPlanBin
GROUP BY box) i ON m.id = i.id) A
on b.box = a.box
WHERE b.Box between @FirstBox and @LastBox
ORDER BY A.Box
/* set nocount on */
return

There are some crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. I don't know if these
will help. There are also some reports in the calendar reports and corp tech
demos that have layouts determined by code and values stored in the records.

--
Duane Hookom
MS Access MVP

Duane,
Thanks for the info. I am using an ACCESS adp file (front end for a SQL
2000 backend), which appearently does not have all the things in a simple
Access Database, expressly Crosstab Queries. When trying to create a report
Query (I only seem to have one type of query in this set up) a couple things
are missing, "Transform" function, "First" function and "mid" and "val"
funtions and. I downloaded your Concatenate function and can get

that
part

to run to concatenate some items using VB code modules, but the rest

is
lost

to me.
I imported the table and reports into a stand alone ACCESS DB and

found
the

crosstab query, pursued that for a bit, then discarded it as I cannot get
that track to work in the "live" set up.
If you have any guides as to the Project/sql 2000 client /server thing, I
would be most appreciative.

Thanks,
bob

:

Assuming tblJobPlans with field names CustName, JobNum, PlanName, and
BoxLocation and sample data as you suggested...
You should be able to create a crosstab query that makes a grid with

all
the

plan information for a specific pigeon concatenated together. The generic
concatenate function comes from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

The
SQL

of your crosstab would be:

TRANSFORM First(Concatenate("Select CustName & ' ' & JobNum & ' ' & PlanName
FROM tblJobPlans WHERE BoxLocation ='" & [boxLocation] & "'",Chr(13) &
Chr(10))) AS Expr3
SELECT Left([BoxLocation],1) AS BoxNum,

(Val(Mid([BoxLocation],3))-1)\6
AS

Expr1
FROM tblJobPlans
GROUP BY Left([BoxLocation],1), (Val(Mid([BoxLocation],3))-1)\6
PIVOT (Val(Mid([BoxLocation],3))-1) Mod 6+1 In (1,2,3,4,5,6);

You should be able to create a report with the exact contents you want
including the blank pigeon holes.
--
Duane Hookom
MS Access MVP
--

Using Access 2000 database project with Sql Server 2000 Back end.
Issue:
A drafting firm has 60 boxes divided into 36 - 2" squares - 6

across
and 6

down for storing architectural / engineering plans. The Access Project
tracks the Customer Name, Job Number, Plan Name and Box Location

of
these

plan sets (among other info). I have built a report that uses these 4
fields
formatted in 2" squares printed on 17x22 paper cut to fit in the

doors
of

the
cabinets. Works great as long as there is a 1 to 1 ratio of jobs

to
box

locations. Now there are multiple elevations and job numbers for

a
single

box location. The report now generates several "labels of boxes"

in
the

same
box-location. (ie: A-001, A-001,A-001, A-001, A-002, A-003, A-003, A-004,
etc..
Question 1:
Is there a way to create a report that consolidates all of the

same
box

locations into one "box label area" on the report (using the first
instances' Customer
Name, Plan name, etc? Tried Using Group by, Select Distinct,

select
top

1.
None worked.
Question 2:
Is there a way to create a similar/same report that will show the "empty"
boxes even though there is no info for that box location and the box
location
itself is not used in the database? Example: boxes A-001 thru

A-036
have

been filled and four of these plan sets have been

deleted/archived.
The

Box
Location in the Database has been changed to DF ( dead File) from thier
orignal: A-011, A-015, A-024 and A-028. The current report skips these
numbers, putting A-012 where A-013 should be, moving the rest of the
numbers
accordingly.
Question 3:
Related to #2. Can a Report be generated that will "auto-generate"
boxlocations (without any other info) between a set of parameteres (ie:
B-001
thru B-036), that can be used as a blank slate to hand write info

on
until

the box gets full and report #1 can be generated?