Selecting query that will have the number of records as one fields sum

[inso18]

Users and experts,
I have a pretty unusual select query I would like to build.

In a table I have Amount field and EventDate field. I need the number of records to be as the sum of Amount field, in the following way:

Original Table:

Amount EventDate
1 8/10/2002
1 9/10/2002
3 10/10/2002
2 11/10/2002
4 12/10/2002

( 11)

Select Query:

Amount EventDate
1 8/10/2002
1 9/10/2002
1 10/10/2002
1 10/10/2002
1 10/10/2002
1 11/10/2002
1 11/10/2002
1 12/10/2002
1 12/10/2002
1 12/10/2002
1 12/10/2002

( 11)

Any ideas how this is possible to achive?
Thanks for the help.

 

[r937]

create an integers table as follows --

create table integers 
(i integer not null primary key);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

now you can generate as many rows as the Amount value, assuming no Amount value is greater than 9, using the following join

select 1 as Amount
     , EventDate
  from integers
inner
  join yourtable
 where i between 1 and Amount

r937.com | rudy.ca

 

[inso18]

Thanks,

I'm using ms-access sql queries and can't preform the command

Select 1 as Amount, EventDate from integers

as 1 is not a field and EventDate isn't on integers table.

Any suggestions?
Thanks again.

 

[r937]

if you have an access problem, you should probably post your question in the access forum

yes, 1 is not a field, it is a numeric literal

the query should work regardless, assuming EventDate is the name of the column in your table

note that you never did say what your table name was

r937.com | rudy.ca

 

[inso18]

I thought creating an SQL command is the same in ms-access.

the table can be called myTable.

So if I have myTable with the fields Amount and EventDate field and the table Integers with i field, what is the correct command? And is it possible at all to combine it, considering I'm using ms-access?

 

[r937]

did you try the query i gave you?

r937.com | rudy.ca

 

[inso18]

Yes, I did.

 

[r937]

and what happened?

r937.com | rudy.ca

 

[inso18]

It didn't work, but some someone have combined the query that works for me. If you're intersted I can ask those who have helped me if it's ok to post it.

 

[inso18]

I'm sure you know what you're talking about with your code, but maybe I haven't explained correctly the situation, or you haven't understood me.