advanced querie problem

[GaryPanic]

Hello gurus
I have a d/base (Access 2000) and within this I need to filter out some records


insurnace related
so I have a location code of ten venues coded 2-11 in a table
and I need to extract out informaiton on all of these in one table

I date a date of event and a sum insured , what I need is date difference between date to be enter on a form (default Now()) and the date of the event - this is the easy bit

Now i need to split these out in batches of 30 days into columns
ie 1-30 days
31-60 days
61-90
etc until >240 days

the sums insured need to be in the right date column

example event 1 sum insured @ 25,000 is 45 days away from now so it should appear in the 31-60 column and if there more than 2 events at that venue one being 45 days and the other being 55 and with sums insured @ 25,000 and 35,000 I need them to either be on a line each or combined to give a total of 60,000 in the 31-60 date column


There could be up to 20,000 - to 100,000 of which the code will extract the venue codes of 2-11 and these could be high hundreds records to check and this will be a regularly run question

any thoughts on this would be welcome - each appraoch I take seems to weird and getting nowhere -

i thought about putting a date difference field on my table but it doesn't seem liek the right thing to do as I would have to update this consantly or rather remember to run this query before I run my report - and this just seems wrong

any thoughts

 

[giovi2002]

Of course, the generated query will be wrongly generated by access.
You'll have to make subqueries by hand and use union all


Make a new Query

Select your table

make a new expression called:

date()-[tablename]![tabledatefield]
(replace tablename and tabledatefield by your database datefield)

Within the criteria of the 1st expression set it to >=0 And <=30

Goto the SQL button on your query

SELECT Date()-[tablename]![DATEFIELD] AS GROUP1
FROM YOURTABEL
WHERE (((Date()-[tablename]![DATEFIELD])>=0 And (Date()-[tablename]![DATEFIELD])<=30))


- COPY THE CODE IN THE SAME WINDOW UNDERNEATH THE QUERY. BEFORE YOU DO THIS PUT UNION ALL UNDERNEATH THE QUERY

SELECT Date()-[tablename]![DATEFIELD] AS GROUP1
FROM YOURTABEL
WHERE (((Date()-[tablename]![DATEFIELD])>=0 And (Date()-[tablename]![DATEFIELD])<=30))
UNION ALL

- NOW PASTE THE CODE
SELECT Date()-[tablename]![DATEFIELD] AS GROUP1, NULL AS GROUP2
FROM YOURTABEL
WHERE (((Date()-[tablename]![DATEFIELD])>=0 And (Date()-[tablename]![DATEFIELD])<=30))
UNION ALL
SELECT NULL AS GROUP 1, Date()-[tablename]![DATEFIELD] AS GROUP2
FROM YOURTABEL
WHERE (((Date()-[tablename]![DATEFIELD])>30 And (Date()-[tablename]![DATEFIELD])<=60))

ADD AS MANY GROUPS AS YOU WANT, MAKE SURE ALL 'UNIONED' QUERIES HAVE THE SAME NUMBER OF GROUPS IN THE SELECT STATEMENT.
MODIFY THE WHERE STATEMENT FOR EACH GROUP TO MEET YOUR CRITERIA PER GROUP

THIS WILL GIVE YOU A QUERY TO MAKE A REPORT
YOUR SUMMATION PER GROUP WILL BE SUM1 AND SUM2 etcetera ,also list the sums in the select statements.

 

[GaryPanic]

thanks

giovi2002

great sounds simple - will need to digest this ,on a steep learning curve as this is going into unknow terrororties for me. seem something like it before , and was greatful that someone esle had to do it , now down to me and I am greatful for the pointers, you and many other users of this forum have passed on to me and others, normally i just sit on something that someone esle has raised and tweak whatever their answer is,

many thanks in advance

now to actual attempt this