Monthly Billing / Invoice (1 Viewer)

Samadkhan

Registered User.
Local time
Today, 14:15
Joined
Sep 23, 2018
Messages
31
I have created four tables with relationship of One to many - tblMucadamJOB, tblMucadamDta, tblMucadamInvoice and tblMucadamStkRprt.

We open a job which remain with us for a year and required monthly billing tblMucadamInvoice(table) ..
The billing information could be fetched from tblMucadamJOB table

How can I create monthly billing ?


Thanks
 

Attachments

  • Untitled.png
    Untitled.png
    154.7 KB · Views: 72
Last edited:

Samadkhan

Registered User.
Local time
Today, 14:15
Joined
Sep 23, 2018
Messages
31
Simple answer for a simple question: build a report.

I want to create a button which generate the report, is there any codes to perform this job? please help me out with thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:15
Joined
Feb 28, 2001
Messages
27,001
I'll get part of this started.

To get monthly billing, you need to have dates in your dataset so that you can tell which month contains which items to be billed. No dates? No monthly report!

If you have dates in the records, then you can build a query that includes a WHERE clause to select the records that fall into your particular billing cycle. This could be based on a "ItemDate Between CycleStart And CycleEnd" - but you would have to find ways to supply the cycle start and end dates. Since I have no clue as to the structure of your application, I have no way to be more specific.

Once you have the query, you can build a report based on it. There are report wizards that would work based off of a query.

After that, to make it "a touch of a button" you can build a button on a form (since buttons require someplace to be in order to be usable). One of the button wizards allows you the option to open a report. So you would build the button, use that wizard, and then go back in to edit the code it built to open it in a way that prints it. The "OpenReport" action from DoCmd is something you can look up to see how it does this specific part.

Without a lot more info about your DB structure, none of us would be able to take it farther.
 

Samadkhan

Registered User.
Local time
Today, 14:15
Joined
Sep 23, 2018
Messages
31
Hi all,

I have attached table relationships snap and hope it may helps to sort my issue.

Thanks all
 

Attachments

  • Untitled.png
    Untitled.png
    76.5 KB · Views: 70

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:15
Joined
Feb 28, 2001
Messages
27,001
It would appear that you have 10 tables, each one paired with another table, to define five two-way relationships. Then you have the Job, Data, Invoice, and Report tables. But I see only two sets of dates in the 4-way relationship. The way that is structured, I don't see a clear path between the only thing that looks like usable dates and two of the three other tables. You have dates in what APPEARS to be a dependent table but I'm not sure. I looked in the 10 "side tables" but didn't see anything in that image that could drive date-based reports. So I at the moment am not seeing how to get to point B from where you are.

This does not mean you can't. It means I can't see the path because the dates, at least at first blush, do not appear to be in a usable position for the kind of JOIN that you need. That diagram SEEMS to indicate 1/1 relationships among your "big four" tables based on the way the connectors terminate and that also seems structurally questionable.

What is the intended BUSINESS relationship represented by those tables? That might clarify matters. Do not talk "programming jargon" when you answer the question. Tell us what the tables represent in your business model.
 

Users who are viewing this thread

Top Bottom