Help on reference number for database (1 Viewer)

[hohai610]

Hello everyone
I'm working on my own MS Access for rental apartment database. I 'm getting stuck at the payment table. So basicly I have rent and serviced charges( electricity, water) need to be collected . But the first month , Tenants only pay Rent ( begining of the month ) and pay the rest fees (at the end of that month). So Problem happened here is reference number , every month 's Bills (except the First month) have Rent fee for next month and serviced charges for the last month .
PLEASE GIVE ADVICES THANK YOU SO MUCH every one

 

[CJ_London]

no idea what you mean by reference number, please provide some realistic example data and the outcome required from that example data

 

[The_Doc_Man]

So what you are saying is that you pay rent in advance but utilities/other fees after use.

Our problem is to understand how you are doing (or trying to do) the billing. Are you trying to develop a query for the month or are you trying to use VBA code to do this? The answers we give might be different for those cases.

 

[Pat Hartman]

You will need to use a union query. Get the month for the rent and the month -1 for the utilities

 

[hohai610]

no idea what you mean by reference number, please provide some realistic example data and the outcome required from that example data

So For example .The Tenants signs with me a 6 month contract (monthly payment) . I would like to make a payment reference number for each month(which is combined by month range and contract # that on the contract) .But that would be mixed up because First month utilities and second month rent fee would be on a same reference #

 

[hohai610]

So what you are saying is that you pay rent in advance but utilities/other fees after use.

Our problem is to understand how you are doing (or trying to do) the billing. Are you trying to develop a query for the month or are you trying to use VBA code to do this? The answers we give might be different for those cases.

Yes Mr The_Doc_Man .That is what i meant. As i wrote , because it is mixed up ( utilities for last month be on the same bill of upcoming rent fee) .So i dont know how to design tables (rent, water , electricity and other fee) .Most of my access database , I'm doing by VBA .But it would be nice if you can give me your advices for both VBA and Query sir .

 

[hohai610]

You will need to use a union query. Get the month for the rent and the month -1 for the utilities

Thank you so much for your advice .Never use UNion query because (most of my time i work on VBA ).But now i think i need to learn more about queries and table design minset. I will take a look on Union query .

 

[hohai610]

Actually i don't know if the way I'm doing it is right or not so i will explain all my plan about billing system for my database. IF ANYONE HAS ANY IDEAS OR OPINIONS (no matter VBA or query) .Please GIVE!!!
I would like to create payment system ( A form that i can input consumption for Light,Water and maybe extra fee (if that changed) for each month when the time comes).So that would make a bill for customer with all information on that ( upcoming rent +last used of utilities ) and sends them straight to customer phone (messenger, whatsapp,....) . In the past when Customer make wire transfer they note the month and room# ,... but that is hard and waste time for me to know who sent the payment .So that why i thoght i may need a payment reference # (easier to manage when they attached in the note).

 

[Gasman]

You can edit your previous posts you know, rather than adding a new one on every thought?

 

[CJ_London]

Would have thought you would have a table telling you who occupied a room and when

if you want help you need to tell us more about what you have at the moment-tables and relationships plus some example data. Plus an overview of the business and what are you renting? Is a room a hotel room? B&b? Office? Something else?

edit: i should clarify this post - your first post talks about apartments, now you are talking about rooms

 

[hohai610]

You can edit your previous posts you know, rather than adding a new one on every thought?

ok . i noted it thank you

 

[hohai610]

Would have thought you would have a table telling you who occupied a room and when

if you want help you need to tell us more about what you have at the moment-tables and relationships plus some example data. Plus an overview of the business and what are you renting? Is a room a hotel room? B&b? Office? Something else?

edit: i should clarify this post - your first post talks about apartments, now you are talking about rooms

First , English is not my mother tongue language . So sorry if i made any misunderstand or unclear info . I have a rental business that my house have a lot of ....(don;t know what to call rooms or apartments but it's a unit has all convinient in there like) .All of them is for long term renting ,not airbnb not hotel ,....the reason why i dont postup my database because i did used my mother language so it maybe hard for you to understand the table and forms .I will make a english sample data for what i'm having at the momentand how far i got

 

[CJ_London]

A room is a room and an apartment a collection of rooms. Apartments normally have meters so you can take readings of utilities used. Rooms don’t normally have meters.

so need to clarify how you determine utility usage since the implication is it can vary from month to month. Otherwise why not just include in the rental?

other implication is that at the end in the tenancy they will have a one month utility to pay a month later

so I kind of understand you want what sounds to me like an invoice number, just not clear why a) you don’t have this already and b) why your tenants would change this reference every month

 

[hohai610]

A room is a room and an apartment a collection of rooms. Apartments normally have meters so you can take readings of utilities used. Rooms don’t normally have meters.

so need to clarify how you determine utility usage since the implication is it can vary from month to month. Otherwise why not just include in the rental?

other implication is that at the end in the tenancy they will have a one month utility to pay a month later

so I kind of understand you want what sounds to me like an invoice number, just not clear why a) you don’t have this already and b) why your tenants would change this reference every month

Thank you for your patience
Answer to your questions
1.All of my units are apartments (for sure )
2.we have meters for each apartment just for Electricity ( Water and serviced fee are constant )
3.At the beginnign they pay rent in advance and At the end they pay utilities before check out and get back security deposit
4.Yes i want invoice number for each transaction that they made ( for each month)
5.i have invoice number already( a integer column) but after that i need another column that combined by contract # and the range of month they made transaction for exp : contract number 11 , month from July 24 to august 23 . 24072308R11 something like that so when the users looks in that reference they will know what is that for )

 

[CJ_London]

Suggest you need to show some example data in table form, not as a description as it is not clear to me what the problem actually is. Originally thought it was interpreting what the tenant has put on the payment as a reference, now seems to be more to do with what you put on the invoice.

If you are struggling with the English, create the text in your language and then use google translate or similar to translate it for you.

 

[Pat Hartman]

Always tell us when you use google translate. It can produce some strange results for technical conversations but if we know it was used, we can usually make sense of the result.

 

[Cronk]

Use separate fields for Contract No, Invoice Date and Period covered. You can combine these in any printed invoice, or I'd just put the data on the invoice so there does have to be any interpretation.