Pick a specific number?

[David_Skov]

I have my database (ofcourse) and then I made a query which I have choosen to be sorted by year and then by week. I do a small calculation but in that calculation I need to get the info of the weeknumber in the final/last row of the database-query.

So if I have a structure that looks like this:

Year - week - cost
2003 - 2 - 5000
2003 - 3 - 7000
... - ... - ...
2004 - 20 - 5543
2004 - 20 - 8325
2004 - 21 - 2000

The "-" indicates a new cell. I need a formula or a way to be able to know what the week in the last row is (in the example above its 21). But since I would like it to run automatically I would really like to avoid doing any manual work

I have an ID in the first coloum btw. Just ascending numbers from 1,2,3...etc.

 

[David_Skov]

No it doesn't seem so:

SELECT Gallup.År, Gallup.Uge, Gallup.Ugevægt, Gallup.Kanal, Gallup.Kædenavn, Gallup.Antalsider, Gallup.Sidenr, Gallup.Leverandør, Gallup.Produkt, Gallup.Brand, Gallup.Branche, Gallup.Kategori, Gallup.Tilbudspris, Gallup.Normalpris, Gallup.Enhed, Gallup.AntalEnheder, Gallup.Enhedspris, Gallup.Sideandel, Gallup.Måned, Gallup.[Grossist 2], Gallup.Fællesindkøb, Gallup.[Excl COOP], Gallup.ÅrKanalUgeProdukt, Gallup.Kampagner, Gallup.[Beregnet Ugevægt], Gallup.Kædevægt, IIf([Uge]<=DMax("[Uge]","[Gallup Query]","[År] = " & DMax("[År]","[Gallup Query]")),"YTD","-") AS YTD
FROM Gallup
ORDER BY Gallup.År, Gallup.Uge;

But maybe I can't see it

Anyway, tx for your help too and your patience

Everything works just nice

Um, David. Nothing wrong with Bat's fix. In fact, I like it better.
But, in answer to "where does my example go" - the answer is the exact same place you're typing, just in a different form.

What you must be using is the QBE (Query by Example) Wizard interface. That's the one that gives you a pull down menu to select fields, or you can drag and drop them into the grid. Bat17, for example, was talking about dropping info into the criteria field to get your information.

Try something for me. Take that saved query that you just picked up and with it still open, go up to the View Menu in the Access window and select SQL view.

And then a trick question. By any chance does my example above appear in a similar syntax to what you're now looking at?

It will not be exact, because it's a slightly different method.

Mike

 

[mdmilner]

Actually, it's extremely similar.
Look past the differences in length, because quite obviously I could not know your field names and look at structure.

Yours looks like this:

SELECT tbl1.fld1, tbl1.fld2, tbl1.fld3, IIF(*****) AS fld4 
FROM Gallup 
ORDER BY tbl1.fld1, tbl2.fld2;

You have a lot more fields listed, but I've trimmed it down just to make it easier to view.

This was my first suggestion:

SELECT TOP 1  tblData.Initial, tblData.Number, tblData.City, tblData.St, tblData.Location, tblData.File, tblData.Date, DatePart("ww",[Date]) AS Wk 
FROM tblData 
ORDER BY DatePart("ww",[Date]) DESC;

Which could be 'simplified' by removing my tablenames/fieldnames to:
This one takes a date field and calculates the week, and then only shows you the most recent week.

SELECT TOP 1  tbl1.fld1, tbl1.fld2, tbl1.fld3, DatePart("ww",[Date]) AS Wk 
FROM tblData 
ORDER BY DatePart("ww",[Date]) DESC;

Or my second suggestion which looks like:
This one uses your existing week number field without a need to calculate it.

SELECT TOP 1  field1, field2, field3, yourWKfield FROM tblData 
ORDER BY yourWKfield DESC;

As a note, the format of tbl1.fld1 is optional and thrown in by Access.
You could reference the field as fld1 and not have any issues.

Mike