invoices (contracts)

[hello]

Hello,

I have a table with invoices:

ID| Start date | End date | fee
18 1999-01-02 | 2018-01-02 | 3000
18 2012-01-02 | 2018-01-02 | 5000


In my query i can see all years and what the fee has been.

The problem is if customer has an old contract, example 1999-01-02 to 2018-01-02 and I add a new contract with the date 2012-01-02 to 2018-01-02, I will get two fees for each year from 2011 and further on.

I want a new contract to replace the old contract, but only from the "start date" on the new contract. How can i Solve this?

Thanks!

 

[vbaInet]

You're not keeping a record of previous start and end dates?

 

[hello]

You're not keeping a record of previous start and end dates?

Yes, I am. I want a history in my query.
I just want to be able to add a new contract without changing the old one.

Any ideas?

 

[dansc0tt]

Can you show an example of the output you have (and what you are looking for) from the query please?

 

[hello]

Can you show an example of the output you have (and what you are looking for) from the query please?

Yes, i will do that as soon im home frome work. Thanks a lot

 

[vbaInet]

So basically what you're saying is you want the most recent contract row in the table? What are you using the data for and do you need all fields returned?

 

[hello]

Im not home from work yet but yes, I need all rows.
I`m only sending an invoice once a year. And the inoice is adjusted to cosumer price index.

If i can get this query to work then i can look back in the past and by law i am able to invoice them ten years back. For example one customer paid to little six years ago, then i can srnd a new invoice on that diff. Sry my english, Im on my cellphone

Thanks

 

[vbaInet]

Im not home from work yet but yes, I need all rows.

Well that doesn't really answer the question.

Perhaps reply back when you're more settled.

 

[hello]

Okay, now I have added an attachement of my query.

Debitera=Invoice
Sumofinbe...= How much the customer paid that year.

They aren´t equal but that´s correct.

Anyway, as you can see in the picture there will be two invoices becuase I have two contract.

The old contract was from 2005-2020 and the new contract is from 2011-2020
I don´t want to remove the old contract since i want to keep a history in my access database. Instead i want access to stop calculating on my old contract if there is a new contract.

How can I solve this?

I hope you understand.
Thanks

 

[dansc0tt]

Try performing a query on these results that uses a 'Group By' on all but the 'Fee' field and 'Last' on the 'Fee' field.

 

[hello]

I'm @ work again. How would that solve it? I still would have two contracts?
Thanks

 

[vbaInet]

You want to display ALL rows (i.e. old and new) BUT you only want it to calculate on the current contract?

 

[hello]

Yes and no!. If they are overlaping each other - calculate the new one.
Eg.
Old contract 1995-2020
New contract 2011-2020

Stop calculate the old contract at 2010 since the old contract got replaced.

Any idea?

 

[vbaInet]

It's not making sense to me. Best thing to do is upload an Excel file that will include some sample data from the relevant tables, and the output you expect to get from that sample file. That way we can see exactly what you're getting at.

A quick explanation of how you derived at the expected output would be useful too.

 

[hello]

Hi,

Please see attached file.
I hope you will understand now

The reason why I want it this way is because i want a history.

 

[vbaInet]

Right, in the spreadsheet you've shown me the raw data, but I would also like to see a table that shows the results you expect. I want to see your expected output in a separate section (or tab) in the Excel file.

 

[hello]

Right, in the spreadsheet you've shown me the raw data, but I would also like to see a table that shows the results you expect. I want to see your expected output in a separate section (or tab) in the Excel file.

Hi,

I have now uploaded the file again.
Thanks!

 

[khawar]

File is attached as per your requirement

Check the query "YourRequirement"