Inserting Autonumber Field to Query (1 Viewer)

darth sidious

Registered User.
Local time
Today, 02:40
Joined
Feb 28, 2013
Messages
86
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
 

Attachments

  • MusicDatabase.accdb
    844 KB · Views: 81

Ranman256

Well-known member
Local time
Today, 05:40
Joined
Apr 9, 2015
Messages
4,339
you cant add autonumber to queries. You assign them to tables, then append query data to the table. (but not the autonum)
 

darth sidious

Registered User.
Local time
Today, 02:40
Joined
Feb 28, 2013
Messages
86
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

Well-known member
Local time
Today, 05:40
Joined
Apr 9, 2015
Messages
4,339
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

Registered User.
Local time
Today, 02:40
Joined
Feb 28, 2013
Messages
86
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

Registered User.
Local time
Today, 02:40
Joined
Feb 28, 2013
Messages
86
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

Registered User.
Local time
Today, 02:40
Joined
Feb 28, 2013
Messages
86
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

AWF VIP
Local time
Today, 02:40
Joined
Oct 17, 2014
Messages
3,506
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
 

Attachments

  • AutoNumberInQuery.accdb
    388 KB · Views: 94

darth sidious

Registered User.
Local time
Today, 02:40
Joined
Feb 28, 2013
Messages
86
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

AWF VIP
Local time
Today, 02:40
Joined
Oct 17, 2014
Messages
3,506
I zipped it. Maybe that will help.

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

Attachments

  • AutoNumberInQuery.zip
    18.7 KB · Views: 72

sneuberg

AWF VIP
Local time
Today, 02:40
Joined
Oct 17, 2014
Messages
3,506
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

Registered User.
Local time
Today, 02:40
Joined
Feb 28, 2013
Messages
86
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

AWF VIP
Local time
Today, 02:40
Joined
Oct 17, 2014
Messages
3,506
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?
 

Attachments

  • Screen Shot.jpg
    Screen Shot.jpg
    53.3 KB · Views: 85

Users who are viewing this thread

Top Bottom