Query

[Tragac011]

Can I solve this in query

I have customer that stay in hotel from example 01/09/2014 to 20/09/2014 and they paid for that some money. And I have customer that stay from 10/09/2014 to 24/09/2014. I need query that will split the money to every day from 01/09/2014 to 24/09/2014 to see for every day income.But I also have one more problem with this, becouse first and last day can have half of price from ordinary days. Exampe. They stay 3 day and pay 5 something, I need that first day have 1 other two 2 somethink

 

[Tragac011]

can anybody help me wtih this?

 

[plog]

No, yes and why.

You posted this in the query section, this cannot be done with just a query. You can't create rows of data that don't exist using a query. You are storing your data at a visit level (visit start, visit end), but you want to view the data on a day level. A query alone cannot do that.

Yes, it can be broken out that way using VBA. It would involve reading in your data, looping through it and generating a record for every day necessary. Not impossible even for a beginner, but even for someone who had done it before it would take some time to write the code.

Most importantly, why? What's the end game? How come you need this data in a datasource like that? I assume its for some sort of output (Access Report, Excel Spreadsheet). What's the ultimate use for this data that you need it like this? Maybe there's a simplier way than this method.

 

[Tragac011]

No, yes and why.

You posted this in the query section, this cannot be done with just a query. You can't create rows of data that don't exist using a query. You are storing your data at a visit level (visit start, visit end), but you want to view the data on a day level. A query alone cannot do that.

Yes, it can be broken out that way using VBA. It would involve reading in your data, looping through it and generating a record for every day necessary. Not impossible even for a beginner, but even for someone who had done it before it would take some time to write the code.

Most importantly, why? What's the end game? How come you need this data in a datasource like that? I assume its for some sort of output (Access Report, Excel Spreadsheet). What's the ultimate use for this data that you need it like this? Maybe there's a simplier way than this method.

I want/need to know did I earn money for every to know can I pay wages for every praticulari day. If I cann't I need to split income for that day betweeen workers .
What you reccomend to do, do that in table?

 

[plog]

Can you post sample data from all relevant tables (include field and table names) and then based on that sample data what you expect as the results?