Simple table, diffucult report, can anyone help me out?

[Johnrg]

Guys,
I am a very basic user.
I have a simple table that has a shows my stock of lumber.
Each bundle has a pack number and this is repeated in the table once if the bundle has one qty and length record, but can be repeated multiple times if the bundle has many qty and length records.
Can anyone make a simple report for me that gets this data for each bundle onto just ONE line of the report, left to right.
I have tried and tried but the report always displays each qty/length records down the page and runs over many many pages. I get a report that has 8500+ lines but there are really only unique 3000 bundles of lumber so the report should only have that many lines.
I just have no idea where to start but will try and look at the code and learn from it if some one could help me out.
I would be truly thankfull.
The DB is attached with the table in it.
JohnG

 

[bob fitz]

Can you attach a copy of the db in A2003 mdb format.

 

[Isskint]

john, hi

If i read your enquiry right, currently you get something like this;
Bundle 1
......Pack 1 Qty 2 Length 3
......Pack 1 Qty 1 Length 2
......Pack 1 Qty 2 Length 2
etc

But want something like;
Bundle 1
......Pack 1 Qty 5 Length 7 (3 records)
etc

If so the SQL from a query i use to count number of PODs and sum Quantity for each drop on a trip may give you some clues.

SELECT DISTINCT [Active PODs].Drop, [Active PODs].Trip, Count([Active PODs].Drop) AS CountOfDrop, Sum([Active PODs].Qty) AS SumOfQty
FROM [Active PODs]
GROUP BY [Active PODs].Drop, [Active PODs].Trip
HAVING ((([Active PODs].Trip)=Forms![Trip Detail]![Trip Id]))
ORDER BY [Active PODs].Drop;

This returns a list like this;

Drop.Trip………CountOfDropSumOfQty
1……T140608A.1………………7
10….T140608A.1………………1
11….T140608A.3………………25
12….T140608A.1………………1
13….T140608A.2………………18
14….T140608A.1………………2
15….T140608A.1………………10
16….T140608A.1………………6
17….T140608A.2………………37
2……T140608A.1………………7
3…...T140608A.1………………5
4…...T140608A.1………………2
5……T140608A.1………………10
6……T140608A.2………………3
7……T140608A.3………………76
8……T140608A.1………………11
9……T140608A.1………………4

Instead of;
Expr1…...TripDropQty
1…………T140608A.1……7
10………..T140608A.10….1
11………..T140608A.11….20
11………..T140608A.11….3
11………..T140608A.11….2
12………..T140608A.12….1
13………..T140608A.13….0
13………..T140608A. 13….18
14………..T140608A.14….2
15………..T140608A.15….10
16………..T140608A.16….6
17………..T140608A.17….0
17………..T140608A.17….37
2…………T140608A.2……7
3…………T140608A.3……5
4…………T140608A.4……2
5…………T140608A.5……10
6…………T140608A.6……1
6…………T140608A.6……2
7…………T140608A.7……0
7…………T140608A.7……0
7…………T140608A.7……76
8…………T140608A.8……11
9…………T140608A.9……4

 

[Johnrg]

Hi Guys,

I have attached the excel file if that will help to import into 2002/2003.

What I am look at is something like

Pack1 2/2.7, 10/2.4, 15/5.4
Pack2 3/2.1, 6/4.5, 7/4.8 etc

rather than

Pack1 2/2.7
Pack1 10/2.4
Pack1 15/5.4
Pack2 3/2.1
Pack2 6/4.5
Pack2 7/4.8

Thanks

JohnG

 

[Johnrg]

Anyone out there in computer land that can help me out?

 

[Isskint]

John,

I can achieve something like you are looking for by using a subreport with the print columns property set > 1 column. Have a look at the attached and see if this is what you were looking for.

 

[Johnrg]

Hi There,

Thanks for the reply.

Yes, its getting there, however on the report I need it to look like this.

Just one line for each pack, so I can get lots of packs on each page.

Pack Number Product Code Tally (shortest length >)
123456 SG8CCAH3KDPG9045 10/2.4, 6/2.7, 8/3.0
123457 SG8CCAH3KDPG9045 8/4.8, 10/5.4, 16/6.0
123458 INDUTKDRS10050 143/4.8
123459 F7LOSPH31KDRH7035 2/4.5, 119/4.8

Thanks

JohnG

 

[Isskint]

So you just need to add the Product code into the second query and sort the concantenation expression in ascending order.

Also you may have to accept multiple lines. Unless you can say for definite that you would have a maximum number of Qty/Lengths (and so set the column count to that number). If you set the column count to 10, if you had 11 sets of data you would still get a second row

 

[Johnrg]

Hi There,
Thanks for the reply.
I have formatted this report and added the product code field but have a couple of things I can't get it to do.
In most cases all the data should fit one one line, There are usually only 1-5 QTY/length records per pack, so rolls over to two lines every now and then thats OK.
BUT, how do you set the formatting so the QTY/LENGTH data is closer togeather so we get more data on each line? There seems to be a big gap between them, when it could be closed up alot.
Attached formatted DB
Thanks
JohnG

 

[Isskint]

Trial and error with the columns width and spacing value.

Open the SubReport in design view
Go to File>>Page Setup...
Select Columns tab and adjust the Columns Spacing and Column Width values