update a table with multiple queries

[Guest]

HI,

I am currently running access 2000 and I have results which i get from 6
different queries broken into time bands. i.e The query returns counts for
IDs swiped between 0-15 Mins and so on for each query.

I have a Table which contains an ID Filed, Time Open and then columns for
each of the time bands i.e 0-15,16-30 etc.

What I am trying to do is get the results from the queries to go into the
relevant columns, have tried appends although it works i get a lot of
duplication. any ideas would be mucho appreciated

 

[Duane Hookom]

How about providing some sample records, desired output, and possibly a
justification if you plan on storing calculated values?

 

[Guest]

Not A Problem,

Output test data

Results I get from my queries
Qry 1 - ID swipes between 0-15 Mins
ID TimeOfFirstSwipe 0-15Mins
121a 12:00 4
131b 13:00 5
211a 11:15 6

Qry 2 - ID swipes Between 16-30 Mins
ID Timeof FirstSwipe 16-30Mins
121a 10:20 2
131b 11:15 1
172d 12:00 8

etc...upto 240+Mins

What I need showing in my Table:

ID | 0-15Mins |16-30Mins|etc....
121a 4 2
131b 5 1
172d 0 8
211a 6 0

 

[Duane Hookom]

Do you have the original table records? I don't think you need to create all
these queries but I could be wrong. You might be able to create a simple
crosstab possibly using the Partition() function.

 

[Guest]

yes I do. However it contains a lot of other data which is not necessary to
this exercise. problem I am getting is that in order to get a count on the
times I have found I need to run the x amount of queries firstly to get the
time frames and then to count these up.

thanks for the quick responses much appreciated.....

 

[Duane Hookom]

Create a union query of all the IDs from all the queries. Then create a
query with the union query and all the individual queries by joining the ID
fields. All joins should be set to display all the records from the union
query.