Querry to automate previous record HELP!

[rajgoyal00]

I am BRAND NEW to ACCESS. Have been working on it for a week and have learned a lot. I LOVE the software but not the fact that it isn't easy. By now I have 6 tables, 10 queries and 3 reports, Everything's working beautifully. (with maybe one fix I need to find the answer to) BUT I am stuck at this particular criteria. HELP PLZZZZ.



I have a Table "Lottery" which has 5 fields. "ID(Primary ID), Slot No., Ticket Amount, Ticket Number, Dateval". The purpose is to find Lottery sold every day.

Slot No. "We have 40 Solts, every slot holds a different Ticket"

Ticket Amount " Tickets have different $ values ( some are $1, $2, $3, $5, or $10)

Ticket Number " Each ticket has a serial no."

Dateval "Date"

Each Lottery pack is worth $300 (So $10 ticket would have 30, and $1 ticket would have 300 tickets, $2 would have 150 tickets and so on.)
To get what sold today,
I do:
"Today's ticket No. -- Yesterday's ticket No = No. of Tickets Sold"
then "No. of Tickets Sold X Ticket Amount = Tickets sold"

I do that for each box and Take a sum of all the "Tickets Sold" to Get "TOTAL TICKETS SOLD"

So Far:
A Table with 5 fields. "ID(Primary ID), Slot No., Ticket Amount, Ticket Number, Date"
A Query: Which has
1. Date
2. Slot No.
3. Ticket Number
4. Previous Ticket No "Previous Ticket Number: DLookUp("[Ticket Number]","[Lottery]","
[ID]=" & [ID]-1)" WHICH IS NOT GIVING ME WHAT I WANT

I need this query to give previous ticket no. for that Particular SOLT NO. and the previous date.

I AM STUCK, I thought I could do this query, and then another query that would do "No. of tickets sold" and then maybe another one to get the sum for that date.

PLEASE HELP.... ( Am in going in the right direction, is this a right way to structure this)
IF YES how to make it possible.......



THANKS

[alansidman]

Crosspost: http://forums.aspfree.com/microsoft-...tml#post940498

4. Previous Ticket No "Previous Ticket Number: DLookUp("[Ticket Number]","[Lottery]","
[ID]=" & [ID]-1)" WHICH IS NOT GIVING ME WHAT I WANT

What is this expression giving you if not the previous ticket number?

[rajgoyal00]

Crosspost: http://forums.aspfree.com/microsoft-...tml#post940498



What is this expression giving you if not the previous ticket number?

It is returning a value. But the last input input in for that date. Not by the box no. I need a criteria where it shows me yesterday's ticket numbers entered in each box no.
Thanks for the reply.

[alansidman]

Are the ticket numbers issued sequentially? What format are the ticket numbers? Text? integers?

[rajgoyal00]

Are the ticket numbers issued sequentially? What format are the ticket numbers? Text? integers?

It's a number (integer) and the first ticket we sell is 000 and the last is 299( on $1 )
So on $2 (000 to 149), $3 (000-099) $5(000-059) $10(000-029) making them all $300 a pack.

[alansidman]

Ok. I would create an aggregate query that grouped by Slot nr. and Date and Max on the Ticket nr.

Look here

http://www.techonthenet.com/access/f...umeric/max.php

[rajgoyal00]

I used it, But didn't give out the expected result. Slot 2 brings out the value of Slot 1 as a previous value for each date. Any more suggestions ?? plz

[alansidman]

Without seeing your db, I am at a loss right now. Post your db with sample data . Run a compact and repair before you upload to this forum.

Alan

[rajgoyal00]

Here's a sample I have created for the purpose. I have also attached a excel sheet (the was had it for calculation purposes). Plz change the extention of the .txt file to .xlsx.

Sometimes The lottery pack ends and we put a new pack in it which will start with a new series. I kind of cheated around it in my excel file u will see. and sometimes it might be a different $ ticket in the same slot.. So in the form (where previous ticket no. will auto populate, I should be able to put a new ticket no. or $ amount in it. (ofcourse not changing the previous day's data). I am searching for solutions online but Am just stuck at this particular problem, (which I will also at a different part of the same database).
awaiting ur reply. THANKS

[rajgoyal00]

The attachments somehow didn't uplpad with the last post. Here they are.