Separate Different Type Agreements

[Tinny]

Hello everyone … I am a Access Newbie and I have simple problem. My mind is stack and I can find the solution ….maybe it’s really simple but I don’t have a free time It’s hard.. So the problem is that I make one Database in Access (Really nice and simple program) .It;s about one Insurance company and my tables is

1 Customers
2 Agreements
3 Traffic (about the money)

So the customer is one but he can have a lot of agreements (In this case it’s about cars and I use Card Ids) and in the end I can see about the car-agreement traffic. 1 costumer 1 or more agreements and in the end the agreement traffic
My problem is that the agreements are for 6 months and for 1 year …I don know how can I I separate them… I need a switch (with 2 only types) Here is the relations in my Database … if you can help me that I would be nice form me ..Thank you

 

[Oldsoftboss]

You need to change your table structure slightly and add a "join" table

 

[gemma-the-husky]

You use a query - I assume the AgreementType in your agreement table separates 6months and 12months agreements.

There is a criteria row that you can use to pick particular agreements. In this case put a criteria in the AgreementType column.

 

[Tinny]

Oldsoftboss
You Are The --ONE-- thank you I'll try It..Thanks

 

[Tinny]

Here we Go!

Ok here is my Db ...as you can see in the agrrement types there is a carID and went you choose it in the last form you can see the traffic of the car id (you can see the relations also) So when you add a new agreement you can choose for 6months or one year and then see the traffic in the last form ... i need this because in the future i think i can separete the agreement type. If ask me to tell theme all the agreements from 6months (and the carId traffic) or the Years Agreements Here is my Db and you can see it..The way i use agreement type is rong but it works the carid and the traffic




Ok here is the idea of the hole project

• Add new customer

• Add new agreement type (One Year- 6Months)

• Add new agreement (the customer can have more than one agreements. In one year type or 6months type or both)
  

|-------|1 Year Agreement------ |
Customer------ | |Or Both
|-------|6Months Agreement --- |



In the agreemet you insert the carId
And in th end….

• View the traffic of the CarId

(witch is in one customer(and can have more than one carIds or Agreements))
I doit in my Db all of this but with out the different agreement types.. the reason I think to do that is that I can separate the agreement types for reports etc

 

[Oldsoftboss]

Will have a look

 

[Tinny]

OldsofBoss I Find The Solution ...Only with a simple query..Thank you enyway for help Leary ..I can separe in the end with

SELECT tbl_Customers.LastName, tbl_Customers.FisrtName, tbl_Agreements.CarId, tbl_Agreements.AgreementFistMonthStart, tbl_Agreements.AgreementType, tbl_Customers.Address, tbl_Customers.Tel, tbl_Transactions.Debit, tbl_Transactions.Credit, tbl_Transactions.Debit, tbl_Transactions.Credit, tbl_Transactions.Total
FROM tbl_Customers INNER JOIN (tbl_Agreements INNER JOIN tbl_Transactions ON tbl_Agreements.AgreementId=tbl_Transactions.AgreementId) ON tbl_Customers.CustomerId=tbl_Agreements.CustomerId
GROUP BY tbl_Customers.LastName, tbl_Customers.FisrtName, tbl_Agreements.CarId, tbl_Agreements.AgreementFistMonthStart, tbl_Agreements.AgreementType, tbl_Customers.Address, tbl_Customers.Tel, tbl_Transactions.Debit, tbl_Transactions.Credit, tbl_Transactions.Total
HAVING (((tbl_Agreements.AgreementType)=[?]))
ORDER BY tbl_Customers.LastName, tbl_Agreements.AgreementFistMonthStart;