Help with repeat rows based on number field (1 Viewer)

HI!

i have two tables, temp_tbl_SubProduct_Out and tblCount.

I need to achieve additional rows based on the field Qty_Count. For e.g. if the Qty_Count is 3, then it should repeat 3 rows after running the query.

I have the below query which gives me the result with an additional row, so in the above e.g. it gives me 4 instead of 3.

SELECT temp_tbl_SubProduct_Out.*
FROM temp_tbl_SubProduct_Out INNER JOIN tblCount ON tblCount.CountID <= temp_tbl_SubProduct_Out.Qty_Count;

Click to expand...

1) How can i get the same count as mentioned in the field Qty_Count (refer snapshot pls)
2) If i have to do this by writing vba code function. can anyone help me with that.

A. Why? Don't answer me in terms of this query though, give me the big picture. What are you ultimately trying to accomplish.

B. This can't be done with just a query. You can do it with some VBA dynamic query writing trickery, but I'm not convinced that's even the best way to accomplish whatever it is you are ultimately trying to do.

C. Why do you have a temporary table? Is that the result of a MAKE TABLE query?

Thanks polg for looking at this. My response in line with your questions.

A. Why? Don't answer me in terms of this query though, give me the big picture. What are you ultimately trying to accomplish.

B. This can't be done with just a query. You can do it with some VBA dynamic query writing trickery, but I'm not convinced that's even the best way to accomplish whatever it is you are ultimately trying to do.
C. Why do you have a temporary table? Is that the result of a MAKE TABLE query?

Hope this answers.

Inventory tracking is a solved problem. I would search this site or even google for database that do that. You're reinventing the wheel and throwing in some right angles. Essentially you create a table and credit and debit inventory as needed:

Inventory
inv_Date, inv_Product, inv_Amount
1/1/2018, 1351, 750
1/1/2018, 1411, 25
1/2/2018, 1351, -3
1/3/2018, 1351, -7
1/3/2018, 1411, -2


Then when I want inventory levels I do a simple aggregate query to sum the inv_Amount.


Also, MAKE tables and APPEND queries are generally hacks around a poor structure. Data shouldn't be moved hither and yon in a database. It stays in one table, you relate it to other tables, use queries as necessary and generate final data in that manner.

I really think you need to revist your tables.

Thanks for the suggestion, i have searched for this on the net but was not able to find one. If have any sample in access that you can share. I doubt if i can do as suggested.

Can you set up your relationships tools and post a screenshot of it back here? Try and show all fields in all tables.

lookforsmt said:

Thanks for the suggestion, i have searched for this on the net but was not able to find one. If have any sample in access that you can share. I doubt if i can do as suggested.

Click to expand...

I would suggest on google using
"access sample inventory database free"

You should have multiple results. They may not work as you are asking because they are using standard ways of doing inventory. They may not do spoilage the way you are use to, but I'd suggest looking through some of them to see if any meet your needs before you start from scratch.