Query to display none payers!

[phillsheen]

Hi,
Ive come up against a brick wall once again in my database so once again here we go.

I have my main from Members which contains a field traffstatus, this field will be filled in with either Green, Amber or Red. Green is some one who is up to date with there payments, Amber is someone who has not paid for 4-6 weeks and Red is someone who has not paid for 6 weeks or more. This will be calculated from the current date so some one who has not made a payment in 5 weeks from today will be amber. The payment information is stored in a table named download20060602. This contains 5 fields, ID / datepaid / description / type / amount. The description field is linked to the description field in the members table so that one member can have many payments.

So I need to know how I can make a query to fill in the traffstatus field on the members form using the criteria above.
Im assuming this would be done by a query but Im not sure.

Any help would be fantastic!
Cheers Phill

 

[cuttsy]

I don't think you should store the status in the table since it can easily be calculated on the fly.

Also I am not sure how your relationship between Members and the brilliantly named download20060602 table works.

The description field is linked to the description field in the members table

what is the description field in the members table?

 

[KeithG]

Do you have a memeber ID field? Why are you linking on the description field?

 

[phillsheen]

Thanks for the replies.

what is the description field in the members table?

Do you have a memeber ID field? Why are you linking on the description field?

ok I should explain a little about the download20060602 table. this table is an imported table that the boss gives me every month as a spread sheet from the bank so the fields and data in that table i cant really change. on my members form I have a subform of the download20060602. The description on the download20060602 is a description given by the bank (for example PHILLS0001), the description on the members form is the same manually imputted and therefor in the download20060602 subform all the payments that member has paid will be displayed.
Yes I have a member ID field but i cannot link the id fields as the information is imported. Atleast I dont think I can, Im no expert as you can see!

I don't think you should store the status in the table since it can easily be calculated on the fly.

I dont think so either but thats what the boss wants.

Hope this helps and I hope you can give me some ideas, Im at a loss

Cheers
Phill

 

[phillsheen]

I have attached a snap shot of the members form so hopefully this will be a little clearer.

Phill

 

[namliam]

It is quite easy actually....

make a query something like so
Select Discription, Max(DatePaid) as LastPayDate
from download20060602
group by Discription
Having Max(DatePaid) < Date() - 7*6

Save this as qryLatePayers

Now make a second query, this should be what your are looking for.
Select <Insert any needed fields here from the members table>, LastPayDate
FROM tblMembers INNER JOIN qryLatePayers ON tblMembers.BankDescription = qryLatePayers.Description;

Offcourse change to what ever your column and table names are. Tho I agree that download20060602 isnt the most brilliant of names and doesnt conform to a naming convestion (I dont think)

 

[phillsheen]

Now make a second query, this should be what your are looking for.
Select <Insert any needed fields here from the members table>, LastPayDate
FROM tblMembers INNER JOIN qryLatePayers ON tblMembers.BankDescription = qryLatePayers.Description;

Hi,
Thanks for the reply, Ive had a go with this part of the query above and Im not understanding it to well. I dont have a LastPayDate from the members table? What am I doing wrong??

Phill

 

[cuttsy]

I dont have a LastPayDate from the members table?

The LastPayDate is not from the members table but the query qryLatePayers so you need to do it like this.

SELECT tblMember.YourField1, tblMember.Yourfield2, qryLatePayers.LastPayDate
FROM tblMembers INNER JOIN qryLatePayers ON tblMembers.BankDescription = qryLatePayers.Description;

 

[namliam]

Yeah what Cuttsy repeated from me...

LastPayDate is from the first query... I hope you figure it out