combine "item" and "quantity" to one column to print labels

[simonjarvis]

I have a table with 2 columns: item_code and quantity. eg:
shelf 2
fridge 3

I'm trying to create a query that combines the 2 columns so i get a result:
shelf
shelf
fridge
fridge
fridge

ie. the item is repeated by the number that appears in the quantity column.

I want to do this so I can base a report on the new query to print 2 shelf labels and 3 fridge labels.

Hope someone can help me out.

Thanks
Simon

 

[SJMcAbney]

In the query that is bound to the report add a new calculated field:


i..e

NewField: [item_code] & [quantity]


OR, in SQL:

SELECT [item_code] & [quantity] AS NewField
FROM MyTable;

Stewart J. McAbney | Talk History

 

[simonjarvis]

Stewart

Thanks but that just concatenates the 2 fields. I would like the query to create new rows for the multiple items.
Your query returns
shelf2
fridge3

instead of
shelf
shelf
fridge
fridge
fridge

Cheers
Simon

 

[SJMcAbney]

Ah. You won't create a query. You'd be best served to create a new table and loop through the records (via DAO or ADO) and add the new records to that.

i.e.

Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsDest As DAO.Recordset
Dim lngCounter As Long
Set db = CurrentDb
Set rsSource = db.OpenRecordset("YourTable")
Set rsDest = db.OpenRecordset("NewTable")
Do While Not rsSource.EOF
For lngCounter = 1 To rsSource.Fields("Quantity")
With rsDest
.AddNew
.Fields("ItemCode")
.Update
End With
Next lngCounter
rsSource.MoveNext
Loop
rsDest.Close
rsSource.Close
db.Close
Set rsDest = Nothing
Set rsSource = Nothing
Set db = Nothing

Stewart J. McAbney | Talk History

 

[simonjarvis]

i have created a form & a button that links to your code & renamed my existing table YourTable. Do I need to create the NewTable? As I am getting an error:
compile error
invalid use of property

at the line:
.Fields ("ItemCode")

Simon

 

[SJMcAbney]

Yes, create a new table with a field called ItemCode.

You can create another query to empty it too.

i.e.

DELETE * FROM NewTable;

It's your database so you can name the fields and tables, etc. as you wish. The code above just gave example field and table names.

Stewart J. McAbney | Talk History

 

[simonjarvis]

still getting the same error.
This is the code i have:

Private Sub Command0_Click()
' On Error GoTo Err_Command0_Click

Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsDest As DAO.Recordset
Dim lngCounter As Long
Set db = CurrentDb
Set rsSource = db.OpenRecordset("YourTable")
Set rsDest = db.OpenRecordset("NewTable")
Do While Not rsSource.EOF
For lngCounter = 1 To rsSource.Fields("Quantity")
With rsDest
.AddNew
.Fields ("ItemCode")
.Update
End With
Next lngCounter
rsSource.MoveNext
Loop
rsDest.Close
rsSource.Close
db.Close
Set rsDest = Nothing
Set rsSource = Nothing
Set db = Nothing


Exit Sub

Err_Command0_Click:
If (Err = ERR_OBJNOTEXIST) Or (Err = ERR_OBJNOTSET) Or (Err = ERR_CANTMOVE) Then
Resume Next
End If
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

 

[SJMcAbney]

Set rsSource = db.OpenRecordset("YourTable")
Set rsDest = db.OpenRecordset("NewTable")


I'm assuming these are not the names of your recordsets.

Stewart J. McAbney | Talk History

 

[simonjarvis]

the recorsets are called that now - i changed them so they were.
My data is in a table called YourTable, fields ItemCode and Quantity, created an empty table called NewTable field called ItemCode

Cheers
Simon

 

[PHV]

.Fields("ItemCode") = rsSource("ItemCode")

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

 

[simonjarvis]

thank you, thank you, that's perfect.
Cheers
Simon

 

[SJMcAbney]

Whoops, sorry. These things happen when typing off the top of your head.

Stewart J. McAbney | Talk History

 

[simonjarvis]

Stupid question
The code works perfectly, but how can I call it from a macro that I have setup to clear the old table, fill a drop down to select from, then generate the labels based on the resulting table?
Cheers
Simon

 

[PHV]

Stop working with deprecated stuff ...
Convert your macro to VBA.

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

 

[simonjarvis]

OK, in that case in VBA how do you:

delete the contents of the table "order_items"

run the query "orders_query_prompt"

open the report "label"

thanks

 

[PHV]

Take a look at the DoCmd method:
DoCmd.RunSQL "DELETE FROM order_items"
DoCmd.OpenQuery ...
DoCmd.OpenReport ...

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

 

[SJMcAbney]

Slight amendment to the previous answer:

With DoCmd
.SetWarnings False
.RunSQL "DELETE * FROM order_items;"
.SetWarnings True
.OpenQuery "orders_query_prompt"
.OpenReport "label"
End With



Stewart J. McAbney | Talk History

 

[simonjarvis]

OK, in that case in VBA how do you:

delete the contents of the table "order_items"

run the query "orders_query_prompt"

open the report "label"

thanks

 

[SJMcAbney]

Er, look above.

Stewart J. McAbney | Talk History

 

[simonjarvis]

thanks, works 100% now
no idea how I posted that same comment twice

cheers for all your help

Simon