Inserting Autonumber Field to Query

[darth sidious]

Hi

I have a qryResults which I would like to add an autonumber field to but have not managed this with any success. Please look at attached DB and qryResults, any assistance with this is much appreciated.

Thanks

Darth

 

[Ranman256]

you cant add autonumber to queries. You assign them to tables, then append query data to the table. (but not the autonum)

 

[darth sidious]

you cant add autonumber to queries. You assign them to tables, then append query data to the table. (but not the autonum)

Each time I run the query I would want the tabled to be wiped and new data to be added. If I append to a table it will just add on to the previous data right???

 

[Ranman256]

ah, I see. If you want to start the autonum over at zero, you need 2 target tables
1 to hold the new data, 1 a copy of the structure that never gets data ,(to keep autonum at zero)

I have tTarget and tTarget_MT tables
empty both tables then compress the db. (to start autonum at 0)
now everytime you add data to the table

copy tTarget_MT to tTarget
append data to tTarget.

(I have a macro that does this)
copy the virgin empty table to the working table
add data.

 

[darth sidious]

ah, I see. If you want to start the autonum over at zero, you need 2 target tables
1 to hold the new data, 1 a copy of the structure that never gets data ,(to keep autonum at zero)

I have tTarget and tTarget_MT tables
empty both tables then compress the db. (to start autonum at 0)
now everytime you add data to the table

copy tTarget_MT to tTarget
append data to tTarget.

(I have a macro that does this)
copy the virgin empty table to the working table
add data.

is there no way in expression builder to generate an autonumber in the query

 

[darth sidious]

Hi

I wonder if there is a way of using expression builder to get an autonumber field in a query.

Also can it be done on a form?

 

[darth sidious]

I just had a an idea after doing some research is there a rank function I could use in this query that will give me the rank based on number of votes received????

 

[sneuberg]

If you have something in the query that's unique so that an order can be established you can do this with a combination of a subquery, IIF function and DLookup as I have done in the attached database. I suspect that because of the subquery and DLookup that this would get pretty slow with enough records

 

[darth sidious]

If you have something in the query that's unique so that an order can be established you can do this with a combination of a subquery, IIF function and DLookup as I have done in the attached database. I suspect that because of the subquery and DLookup that this would get pretty slow with enough records

Hi Your database does not open. It thinks it is a php file???

 

[sneuberg]

I zipped it. Maybe that will help.

This was created with Access 2013 if that makes any difference.

 

[darth sidious]

I zipped it. Maybe that will help.

This was created with Access 2013 if that makes any difference.

Can the autonumber be created on a form too? If so how?

 

[sneuberg]

I was going to say that all you need to do it make the query the record source of the form, but I tried that at which point I noticed that the query I came up with is not editable. So that probably won't work for you.

If you implement Ranman256's idea the data will be in another table so editing that wouldn't alter the original data either without putting in some code.

Could you tell us a little more about the purpose of this autonumber. Perhaps we can suggest some other way of accomplishing the end goal.

 

[darth sidious]

I was going to say that all you need to do it make the query the record source of the form, but I tried that at which point I noticed that the query I came up with is not editable. So that probably won't work for you.

If you implement Ranman256's idea the data will be in another table so editing that wouldn't alter the original data either without putting in some code.

Could you tell us a little more about the purpose of this autonumber. Perhaps we can suggest some other way of accomplishing the end goal.

The table holds music groups who have been voted for like XFACTOR. What I am trying to achieve is create a form that lists the top three acts in order of votes and then have a field next to it showing the position each has finished(autonumber or rank). I will also have an additional field on the form that will calculate the percentage of votes each group received out of a total of 19.

I'm not sure how any of this can be achieved on the form though.

 

[sneuberg]

I though this might be solved by using the keyword TOP in the query which I thought could give you the top three. Trying this illuminated a problem you seem to have no matter how you do this and that is ties. How are you going to chose the top 3 if there are two or more scores that are in the top three scores? See attached screen shot with this scenario.


In the end if the form is only going to display three results couldn't you just put labels on the form to indicate position?