Forum Discussion
Solved
Criteria for a query
I have a database containing multiple records, and I want to create a query that allows the user to specify how many times the records should be displayed. This should be done without duplicating the original data preferably using a criteria.
Sample:
1 Row
Date: 02/16/2025
Code: 123456789
Description: Test
But if the user types 2 will show a duplicate row.
2 Rows
Date: 02/16/2025
Code: 123456789
Description: Test
Date: 02/16/2025
Code: 123456789
Description: Test
Take this:
1. Create a Helper Table
- Name it tblNumbers
- Add a single field Num (Integer Type)
- Populate it with sequential numbers (1, 2, 3, … up to a reasonable maximum)
2. Create a Query to Dynamically Duplicate Rows
SELECT YourTable.Date, YourTable.Code, YourTable.Description FROM YourTable, tblNumbers WHERE tblNumbers.Num <= [Enter Number of Duplicates];- The [Enter Number of Duplicates] prompt will ask the user for the number of times they want each record to appear.
- The tblNumbers.Num <= ensures that only the specified number of duplicates is included.
3 Replies
Thanks
The helper table Kidd_lp describes is often referred to as a Talley Table. You can also create a similar date based table for date-span tasks involving dates rather than numbers.
Quite often, table driven solutions are easier to implement and maintain than code based solutions in a relational database application.
Take this:
1. Create a Helper Table
- Name it tblNumbers
- Add a single field Num (Integer Type)
- Populate it with sequential numbers (1, 2, 3, … up to a reasonable maximum)
2. Create a Query to Dynamically Duplicate Rows
SELECT YourTable.Date, YourTable.Code, YourTable.Description FROM YourTable, tblNumbers WHERE tblNumbers.Num <= [Enter Number of Duplicates];- The [Enter Number of Duplicates] prompt will ask the user for the number of times they want each record to appear.
- The tblNumbers.Num <= ensures that only the specified number of duplicates is included.
