selecting data from one table and copy them into another

[achrafus]

hi, I know this may look easy for some,but I need help with it. I have three tables "inhabitants","Payement" and "postponedPayement". I need to select from inhabitants those that didn't pay their loan and store their Ids along with the unpayed period into table "postponedPayement". The table "inhabitant" gathers all the inhabitants of a bulding while "Payement" table gathers all the payement made by an speific inhabitant for a specific month. I wonder if someone know how I can do it automaticaly by the end of each month using SQL. thanks in advance It's not shame to ask if we don't know but it's shame to pretend you know when u don't -- modified at 11:53 Thursday 22nd September, 2005

[Colin Angus Mackay]

From your post I think your table structure looks something like this (I've tried to fill in the gaps with information that seems to fit the description - if this is not the case then a more accurate description would be more helpful)

Inhabitants

ID

Payment

InhabitantsID (Foreign key join to Inhabitants.ID)
Date
PaymentAmount

PostponedPayment

InhabitantsID (Foreign key join to Inhabitants.ID)
UnpaidPeriod

Try this query

INSERT INTO PostponedPayment(InhabitantsID, UnpaidPeriod)
SELECT InhabitantsID, COUNT(*) AS UnpaidPeriod
WHERE p.PaymentAmount = 0
GROUP BY InhabitantsID

It will count all the periods unpaid for each inhabitant and insert it in the PostponedPayment table. Does this help?

---

My: Blog | Photos "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

[achrafus]

thank u Mr Mackay, my tables look like this inhabitant ---------- inhabitantId ... info about inhabitant Appartment ---------- AppartementId inhabitantId Amount //the amount that the inhabitant shoul pay monthly Payement --------- inhabitantId AmountPayed //the amount payed is what's paid each month if done date //date the the paid amount PostponedPayement ----------------- InhabitantId amountdue Referencedate //the reference of the unpaid month I need to get the inhabitants that didn't pay for each month and store these in my PostponedPayement table. for example Appartement Inhabitant Payement --------------------| ----------------| ---------------------------- ApptId inhId Amount| InhID | InhaName| inhId |paidAmount|Date --------------------| ------|---------| -------|----------|--------- 177/R1 inh1 150 | inh1 | James | inh1 | 150 |01/08/2005 177/R2 inh2 180 | inh2 | Jone | inh4 | 100 |05/08/2005 177/R3 inh3 150 | inh3 | Greg | 177/R4 inh4 100 | inh4 | mike | in my PostponedPayment table I need to get this for august payments plus what is left from other months PostponedPayement ------------------------------ InhabitantId|AmountDue|Refdate| ------------|---------|-------| inh2 | 180 |08/2005| inh3 | 150 |08/2005| inh1 | 150 |06/2005| I did the following query to get all those who paid and those who didn't : select Appartement.ApptId, date, Payement.Amount from Appartement left join Payement on Payement.ApptId=Appartement.ApptId I get the following --------------------------------- InhabitantId|Amount | date | ------------|---------|----------| inh1 | 150 |01/08/2005| inh4 | 150 |05/08/2005| inh2 | Null | null | inh3 | Null | null | all I need to pik up are the 2 last values in the example and insert them into PostponedPayment Hope it's clearer now!excuse my English But I thank yu very much Thanks to all those who help and those who try to help