MS Access query question

[Jupitus]

Hi guys,

hope someone can help me with this. I have a database of tickets (query log, if you prefer) which has various fields such as the ticket author, priority, time to closure and so on, but also includes the date the ticket was opened and the date the ticket was closed, eg:

Ticket ID---Author---Opened---Closed
0001-------J Bloggs--15/4/05--20/4/05
0002-------F Smith--16/4/05--17/4/05
0003-------A Bell----18/4/05--20/4/05

What I would like to do is prepare a query which is designed to tell me based on the date opened and the date closed, how many tickets were open in the system on any given date, thus:


14/4/05---0
15/4/05---1
16/4/05---2
17/4/05---1
18/4/05---2
19/4/05---2
20/4/05---0

I'm sure this if pretty straightforward, but my skills lie in Excel, not Access If you can help, please post or PM me if you would find it easier to email me.

Thanks for any assistance!

Jup.

 

[Jupitus]

Anyone?

 

[Shovel]

Are the dates just stored as days, or are they entire DateTime objects (and thus contain a time as well)?
If datetime is 100% unique then it makes using a "group by" clause difficult.

Something along the lines of this would get you the count of tickets for yesterday, though those date formats are probably invalid because my mind has vegetated since I got home from work.

select count(*) from tickets
where
  DateOpened > 2005-04-26-00:00:00
and
  DateClosed < 2005-04-27-00:00:00

Access doesn't support anything clever like cursors (err, "loops" to you and me) though, so I'm not entirely sure how you might apply that kind of rule to return a list of all the days in the database.

My SQL isn't really up to it I'm afraid (give me a proper programming language any day), but you might want to look into some kind of date manipulated function. For example (more pseudo code follows)

select count(*) from Tickets
group by datefunction(DateOpened, 'YYYYMMDD')

The idea in the above is a function that manipulates the date into just the day, thus allowing for universal matches. As to whether Access can do that, I'm not sure (I can't ever recall if T-SQL (MS SQL Server) will do that, so don't hope to hard).

So yeah, someone with better SQL knowledge than me (or alternatively, someone whose mind is like vegetable stew right now) dive in a correct my SQL please.

Ben

 

[Jupitus]

Ben, bless you for trying Problem is, I was hoping to get some help for an Access query rather than SQL, as my SQL abilities are even worse than Access! I really appreciate you draining some soup out of your ears on my behalf though

 

[JingleBells]

Hey Jupitus,
I've been messing around with this today at work. I have come up with what might be a solution: http://www.pete-b.co.uk/files/tickets.zip

I have copied your schema, and all you need to do is enter/copy your data into the Tickets table. Then open the form I made and click on the button. Hopefully a table called 'Summary' will be made. This has only been tested on Access 97 though, so VB might have changed. The Code is in the form source if you want to have a look. It produces the table starting from the first Opened Date and runs to the last closed date.

Hope it works

 

[Jupitus]

Thanks JB! I'll take a look!!