how to display multiple records as much as quantity 1

[davyre]

Hi,

I need to make a report based on a query that joins multiple tables.
Until now, I have made the query that shows the OrderID, OrderDate, OrderDesc, UnitDEliveryDate, UnitID, BSUnitID, QtyOrdered.

Now what I want is that each unit(UnitID, BSUnitID) being displayed as much as the QtyOrdered value, i.e:

_________________________________________________
|OrderID|OrderDate|OrderDesc|UDD|UnitID|BSUnitID|
|     1 |20/02/013|Stock1   |   |41    |YCJSF-1 |  3x (because the QtyOrdered is 3)
|     1 |20/02/013|Stock1   |   |41    |YCJSF-1 |  
|     1 |20/02/013|Stock1   |   |41    |YCJSF-1 |
|     1 |20/02/013|Stock1   |   |33    |TF-461  |  2x (because the QtyOrdered is 2)
|     1 |20/02/013|Stock1   |   |33    |TF-461  |
|     1 |20/02/013|Stock1   |   |8     |UFWWD-2 |  1x (because the QtyOrdered is 1)
-------------------------------------------------

etc.

anyone can help? THanks

 

[dhookom]

I typically have a table of numbers [tblNums] with a single numeric field [Num] and values 1 through some big number. You can then create a query with tblNum and your QtyOrdered field. Set the criteria under Num to QtyOrdered. Don't join tblNum to any other table.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

Hi,

Now I have made the TblNum that has a field Num and values from 1 to 999. I then created a query (query design), but then I dont get what you meant by set criteria under Num to QtyOrdered.

I tried and it says error criteria data type mismatch. Is this correct according to your suggestion?

 

[dhookom]

Put []s around QtyOrdered so it isn't treated like the string "QtyOrdered"

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

I tried but the result is not what I wanted. Maybe I doing it wrong? The result is that the field Num showing numbers that match the QtyOrdered:

 

[dhookom]

Are you sorting by BSUnitID?

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

no, supposedly by OrderID

 

[davyre]

heres the current result:

notice that Num and QtyOrdered are the same
The QtyOrdered are only as a value (i.e not repeating the record as many as its value)
it should be

|Num   |UnitID  |QtyOrdered|
|1     |6       |1
|2     |7       |1
|3     |8       |1
|4     |44      |1
[highlight #FCE94F]|5     |5       |2[/highlight]
[highlight #FCE94F]|6     |5       |2[/highlight]
[highlight #FCAF3E]|7     |13      |2[/highlight]
[highlight #FCAF3E]|8     |13      |2[/highlight]

etc

 

[dhookom]

Either sort by the UnitID or provide the complete SQL of your query. Your "current result" clearly is not sorted by UnitID so it is impossible for us to determine if the query is working or not.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

heres the complete SQL

SELECT TblOrder.OrderID, TblOrder.OrderDate, TblOrder.OrderDesc, TblOrderUnit.UnitDeliveryDate, TblOrderUnit.UnitID, TblUnits.BSUnitID, TblOrderUnit.QtyOrdered
FROM TblUnits INNER JOIN (TblOrder INNER JOIN TblOrderUnit ON TblOrder.OrderID=TblOrderUnit.OrderID) ON TblUnits.UnitID=TblOrderUnit.UnitID;

 

[dhookom]

There's no tblNum in your SQL.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

Sorry, my bad.
This is the SQL with TblNum

SELECT TblNum.Num, TblOrder.OrderID, TblOrder.OrderDate, TblOrder.OrderDesc, TblOrderUnit.UnitDeliveryDate, TblOrderUnit.UnitID, TblUnits.BSUnitID, TblOrderUnit.QtyOrdered
FROM TblNum, TblUnits INNER JOIN (TblOrder INNER JOIN TblOrderUnit ON TblOrder.OrderID = TblOrderUnit.OrderID) ON TblUnits.UnitID = TblOrderUnit.UnitID
WHERE (((TblNum.Num)=[QtyOrdered]))
ORDER BY TblOrderUnit.UnitID;

And the result is still not what I wanted. Can you help me please? Thanks

 

[dhookom]

Use <= not =.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

thanks! It worked!