query question (1 Viewer)

[funkmiesterk]

Hi there,

What i've currently got is a database with 3 tables 1 will be used to store records of upto 6000 claimants with various bits of info another 1 will be used to store claims from these claimants (at least 12 per calendar year per person) and 1 will be used for queries.

the problem i have got at the moment is creating a query that can pull a specific claimants info from the second table into a report without pulling all 6000 at the same time...

is there any way of setting a query that is linked so that when im on (eg) claimant 123 i can click a button and it will create a report for claimant 123 showing all their claims?

any help gratefully received!

kev

ps hi! first post!

 

[boblarson]

and 1 will be used for queries.

You don't need a separate table for queries. You can use queries.

the problem i have got at the moment is creating a query that can pull a specific claimants info from the second table into a report without pulling all 6000 at the same time...

Perhaps if you post your table structure we might be able to help. You haven't given enough information to answer question.

is there any way of setting a query that is linked so that when im on (eg) claimant 123 i can click a button and it will create a report for claimant 123 showing all their claims?

Yes, but as mentioned, we need more info.

ps hi! first post!

Welcome to Access World Forums!

 

[funkmiesterk]

You don't need a separate table for queries. You can use queries.

lol no i mean it will be used for logging queries we receive ie claim being late etc.

ok the info logged in the claimants table is

claimant number
name
budget code
and other non important info (drop down boxes for future reference)

the info logged in the claims table is

claimant number
batch reference
and other non important info eg claim type, claim amount etc

the tables are currently not linked however i was planning to link them via claimant number.

also thnx for the promt response

kev

 

[boblarson]

Yes, as long as you have a common field (usually in one table it is an autonumber and then in the other table it is the same field name for the foreign key but as Long Integer which can store the autonumber from the other table). If claimant number is going to be unique then you can do it similar to that.

Once you have those then you can join the tables in a query to get your data out.

 

[funkmiesterk]

bob thanks again for your reply but i am still having trouble creating this query.

i cant seam to get the query to pull only one claimants claims.

i know its hard to word

what i need is on record 123 is a button that actions a query that pulls any claims that 123 has submitted. but i also need that button to do the same for 124, 125, 126 etc etc.

dunno if this is possible?

kev

 

[boblarson]

If you access this query via a form, then it is reallly easy as you can put this in the click event of the button:

DoCmd.OpenForm "YourFormNameHere", acViewNormal,,"[YourIDFieldInQuery]=" & Me!YourIDFieldOnFormYouAreOpeningFrom

 

[funkmiesterk]

worked a charm! bob- u r a legend!

kev

 

[boblarson]

Glad to hear we gotcha going