You'll need to use a VBA script to manipulate the data to 'expand' out the (effectively) quantities.
How good are you with VBA?
You'll need to open a recordset to search through the 'Months' field, checking if its value is greater than 1. If it is, then it needs to be inserted into a new 'tmp' table. Once the recordset hits the end of the line, the script can then import everything back from the 'tmp' table.
There are other ways to achieve these results, but this is probably the simplest.
You'll also need to keep the order going, probably easiest to insert the data into the 'tmp' table with an autonumber setup to count the months for you.
Thanks for the quick response. Yes, I know some VBA and I can probably figure it out. However, I was hoping to avoid that and use a query instead. I've done something similar before where I do a right join from a smaller table (less records) to a larger table and achieved the "expansion" I am referring to.
Unfortunately I'm more a VBA man than an SQL one, so LEFT JOIN and things are little out of my comfort zone.
I started learning Java and C/C++ a long time ago, and SQL just isn't powerful enough for me to use properly.
I wrote a function a while back in VBA that I used to achieve something similar (although without the months). Here's the code:
Public Function numQuantify(aTable As String)
Dim strQntSQL As String
Dim rs As DAO.Recordset
Dim id As Long
Dim iQnty As Long
Dim sDate As Date
Dim iOrderNum As Long
Dim sCompany As String
Dim sCode As String
Dim i As Long
strQntSQL = "SELECT * " & _
"FROM " & aTable
Set rs = CurrentDb.OpenRecordset(strQntSQL)
Do While Not rs.EOF
iQnty = rs.Fields("Quantity").Value
If (iQnty > 1) Then
rs.Fields("Quantity").Value = 1
'Requires update with each field added
sDate = rs.Fields("Order Date").Value
iOrderNum = rs.Fields("Order Number").Value
sCompany = rs.Fields("Company").Value
sCode = rs.Fields("Product Code").Value
For i = iQnty To 2 Step -1
rs("Order Date").Value = sDate
rs("Order Number").Value = iOrderNum
rs("Company").Value = sCompany
rs("Product Code").Value = sCode
rs("Quantity").Value = 1
'Tidy up to avoid memory leaks
Set rs = Nothing
Will require a bit of adaptation for your use, like moving them to a newly created table with autonumbering for the months, clearing the existing table, and reinserting them back with the autonumber values carried over.
Nothing special, but thought it might help a little.