Is there a way to repeat a record in a query n number of times? 1

[jpkeller55]

I have a simple database set up to print employee name badges using a report to pull data from the employees table including picture. The report is based on a query. Is there a way to return a record multiple times in the query so the user can input how many copies of each name badge to be printed?

 

[Melagan]

thread701-553683 has the answer =)

~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

If you gave me your table name & field name(s), I could help to modify this idea to fit your needs. The thread above takes it's "n" value from stored data in a table. I believe you're wanting the query to prompt you for "n".

Example:

SELECT t.BadgeName, 'part ' & i & ' of ' & [How Many?] AS RunningCount
FROM tblYourTable AS t, integers
WHERE (((integers.i) Between 1 And [How Many?]))
ORDER BY 1, 2;

Where BadgeName is the field that has your employee's name and tblYourTable is the name of the table in which the data resides.

Again this is assuming you have a table called 'integers' with a field called 'i' that is populated with integer values 1 ~ max number you'll need.

~Melagan
______
"It's never too late to become what you might have been.

 

[jpkeller55]

Thanks Melagan for your help!
The table name is: employees
fields: employeeID; name; jobTitle; photo

The photo field is an OLE object.

You are correct, I am hoping to have the user be able to input the number of copies to print of each badge.

Thanks Again.

 

[Melagan]

I'm sorry I didn't catch if you still needed a hand with this or if what I posted was help enough for you to finish your project =) Did you still need a hand?

~Melagan
______
"It's never too late to become what you might have been.

 

[jpkeller55]

Sorry for the confusion...yes, I would love a hand as I am not real sure what I am doing here!

 

[Melagan]

Ok, first of all, did you make the integers table? If not, do so. Thats item #1.
The table should be called 'integers' with one field called 'i'
You'll need to populate this table with integers. If you'll only ever need to print a maximum of 10 duplicate badges, you'll need to create 10 records in your integers table; 1 through 10.

Next, you'll create the following query. Go to queries tab, hit NEW, and just get to the query design grid. You'll want to change your view to "SQL mode", and paste the following code in:

SELECT t.employeeID
, t.name
, t.jobtitle
, t.photo
, 'part ' & i & ' of ' & [How Many?] AS RunningCount
FROM employees AS t, integers
WHERE (((integers.i) Between 1 And [How Many?]))
ORDER BY 1, 2;

That should do the trick.

~Melagan
______
"It's never too late to become what you might have been.

 

[jpkeller55]

Thanks Melagan...works perfectly. Exactly what I was looking for!

jpkeller55