Avatar billede foldager Novice
12. januar 2016 - 09:32 Der er 17 kommentarer og
1 løsning

Nummerér fra 1 i hver gruppe

Jeg har behov for at lave en opdaterings-forespørgsel der kan nummmerere fra 1  forfra indenfor hver gruppe.

Feltet [Sitem] skal have nummeret.
Gruppen defineres af ens værdi i et feltet [NewREDSUB]
Kun rækker der har værdien "Subitem" i feltet [Type] skal have et tal.

http://folda.dk/eksperten/subitem.png

Kan en ekspert mon hjælpe?
Avatar billede terry Ekspert
12. januar 2016 - 10:03 #1
if you only have the fields you show in the png file then its going to be difficult finding a solution which uses an update query only. And I'm not sure using code will be easier either.

The problem is how to sort the table in the first place so that
you get one SubItem followed by 2 CostCode
Avatar billede foldager Novice
12. januar 2016 - 10:09 #2
Sharp as always Terry.

I have a lot of other fields.
I think I have the sorting under control.
I can make a field [SortID] with numbering that will give the right sorting.
Avatar billede terry Ekspert
12. januar 2016 - 10:30 #3
Your welcome to send me an example of the dB and I'll see if I can find a solution, but no guaranties :-)

ekspertenATsanthell.dk
AT = @
Avatar billede foldager Novice
12. januar 2016 - 10:59 #4
Awesome. I will send you an email and hope for the best.
Avatar billede terry Ekspert
12. januar 2016 - 14:42 #5
Got the rar file unpacked.

After looking at the data I've made a query which is very close to what you want.

SELECT VSOutput.NewREDSUBclass, VSOutput.Sitem, VSOutput.Type
FROM VSOutput
ORDER BY VSOutput.NewREDSUBclass, VSOutput.ID, VSOutput.Type DESC;


But I can see that Sitem although sorted correctly doesn't always start from 1.
So, my question is, if you make an update query so we start from 1, what about new data getting appended to the table, how will you ensure these are numbered correctly?
Avatar billede terry Ekspert
12. januar 2016 - 14:43 #6
Forget to ask. I have had to use ID in the sort. Is this OK or can it give problems later?
Avatar billede foldager Novice
12. januar 2016 - 15:15 #7
The Sitems not starting with 1 is wrong data, ignore it.

New data added to the table is not an issue, the data is to be imported into another system.

Using ID is perfectly OK.
Avatar billede terry Ekspert
12. januar 2016 - 15:37 #8
Have you tried the SQL I gave? If I understand you correctly, those where Sitem doesn't start with 1 are to be ignored, then the sItems are 'almost' correct without update. I say 'almost' because I need to check something.
Avatar billede terry Ekspert
12. januar 2016 - 15:48 #9
Can you tell me how the ID which is an autonumber has records with same number?
Avatar billede foldager Novice
12. januar 2016 - 16:08 #10
You are right, it is kind of strange. The ID field is imported from several other tables. There is not an truly autonumbered field at the moment. 

The SQL you gave is only sorting the table as I see it(?).

The Sitem values for rows of Type SubItem should be overwritten by the update query, starting from 1 in each group  followed by 2 and 3 etc. in the right order defined by the sorting of the table.
Avatar billede terry Ekspert
12. januar 2016 - 16:08 #11
I think this query gives you what you want but it doesn't update the table.

SELECT VSOutput.NewREDSUBclass, VSOutput.Type, IIf([Type]='SubItem',(SELECT count(*)+1 from VSOutput AS V where [V]![NewREDSUBclass] = [VSOutput]![NewREDSUBclass] AND [V]![Type] = 'SubItem' AND [V]![ID] < [VSOutput]![ID]),'') AS Sitem
FROM VSOutput
ORDER BY VSOutput.NewREDSUBclass, VSOutput.ID, VSOutput.Type DESC;
Avatar billede foldager Novice
12. januar 2016 - 16:09 #12
Adding a new autonumbered field to keep the sorting is possible but not done yet.
Avatar billede foldager Novice
12. januar 2016 - 16:11 #13
How can I make it update the table?

Sorry for my lack of Access skills :-)
Avatar billede terry Ekspert
12. januar 2016 - 16:14 #14
Not a straight forward way to update table. Normally it would be possible, but because there is a calculated field in query then the table is not updateable.

I'll just try something and get back ...
Avatar billede foldager Novice
12. januar 2016 - 16:16 #15
It seems like the query does the job. I can merge the data in the query using my basic skills. Thanks. Please drop an answer, I would love to give you the points.
Avatar billede terry Ekspert
12. januar 2016 - 16:30 #16
There isn't a unique key on the table, probably why we cant update. So I tried to change ID which is an autonumber to a long integer with the intention of making an new ID field as primary key. I cant, Access wont let me.

So a suggestion is to make a new table but with an new ID (Name it CID)(autonumber) and old ID change to integer (long).

Then alter the query I have made to select all fields and then an insert query to insert into new table.
Obviously use new sitem field
Avatar billede foldager Novice
12. januar 2016 - 17:58 #17
Tusind tak!
Avatar billede terry Ekspert
12. januar 2016 - 18:38 #18
selv tak
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Dyk ned i databasernes verden på et af vores praksisnære Access-kurser

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester