Creating a Dynamic Query 1

[Djbell]

Hi All

What I have is a table with the following info.

Componet Size TotalQuantity AmountperPallet Batchno
Piece A Large 1200 510 57

What I would like is to create a query that will compute the amout of pallets required and create a record for these, so in this example I would want my quety to show the following information.

Componet Size TotalQuantity AmountonPallet Batchno
Piece A Large 1200 510 57
Piece A Large 1200 510 57
Piece A Large 1200 180 57

So the query would work out from the table how many times the AmountperPallet can go into the totalquantity, in this case 2 then work out the remainder in this case 180 and display the 3 records as above.

Can this be achieved?

Regards

Douglas Bell

 

[SkipVought]

Douglas,

Why do you need multiple rows?

You can derive the values from the data at hand...

Number of Pallets: INT([TotalQuantity]/[AmountonPallet])
Remainder: MOD([TotalQuantity],[AmountonPallet])


Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Djbell]

Hi thanks for the reply, I am creating Pallet tickets, So I will need the report to print out the three different amounts on seperate sheets. So Ticket one would show

Ticket 1

Component Piece A
Size Large
Total Quantity 1200
Quantity on Pallet 510
Batch 57

Ticket 2

Component Piece A
Size Large
Total Quantity 1200
Quantity on Pallet 510
Batch 57

Ticket 3

Component Piece A
Size Large
Total Quantity 1200
Quantity on Pallet 180
Batch 57


Regards

Djbell

 

[SkipVought]

Don't believe that you can do that in a query. You'll need to write some code. The basic loop...

sub main()
dim rst as recordset, cnn as connection
...
'open recordset
rst.open ........
'loop thru the recordset
do until(rst.eof)
n = INT([TotalQuantity]/[AmountonPallet])
r = MOD([TotalQuantity],[AmountonPallet])
if n > 0 then
  for i = 1 to n
    PrintLabel [Component],[Size],[Total Quantity],[AmountonPallet],[Batch]
  next
end if
rst.movenext
loop
if r > 0 then PrintLabel [Component],[Size],[Total Quantity],r,[Batch]
end sub
sub PrintLabel(comp as string, siz as string, tq as integer, qty as integer, bch as integer)
  'format and print label
end sub

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Djbell]

Hi

Thanks for the reply again, but you have totally lost me, I have a report setup specifically to read from the query.

Can you explain the above to me a little more, sorry for being a newb.

Regards

Djbell

 

[MajP]

Like Skip did I made a query with calculated fields that counted Number of Full Pallets, Remainder, and also the number of labels to print.

numberLabels: IIf([remainder]<>0,[numberFullpallets]+1,[numberFullpallets])

This accounts for the case where you have full pallets only and no partial pallets, only a partial pallet, and the mixed case.

For ease of writing the code I put the numberFullpallets, numberLabels, and remainder fields all on my form, but hid them.
I also have an unbound field txtAmountOnPallet that I set to either the amount that fits on a full pallet or the remainder.
The key is to use the "on print" event of the detail section, the "PrintCount" which tells how many times the record has been printed, and your needed number of labels.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount < Me.numberLabels Then
'The key is to not move to the next record only
' after you
' have printed the number of labels to print
Me.NextRecord = False
Me.txtBxAmounOnPallet = Me.amountonpallet
Else
'When you get to the last label to print it is either
'a remainder or could still be a full pallet.
If Not Me.remainder = 0 Then
Me.txtBxAmounOnPallet = Me.remainder
End If
End If
End Sub

 

[Djbell]

Superb!!!!!!!!

Thanks for the help. It does exactly what I want it to do.

Regards

Douglas Bell