Saving a form

[Kelev Gadol]

Hi
I'm a novice as far as databases is concerned. I work in the telecoms industry as a surveyor and I have created a db to log all the visits to the sites that I survey. I have a number of tables which include the companies I work for, project names, project codes, services that we offer etc. This all works as well as it needs to although I am aware that it's not perfect in many ways.
I have recently worked out how to create invoices automatically from the data that I input for each site that I visit. I have done this by creating a report from a query. I'm pretty pleased with what I've done, so please don't be too critical! However, the query takes data from the tables based on the date for monthly invoicing to our clients and the report formats this into a separate report for each client. The problem I have is that while the query is valid for that particular month the report/invoice is correct but only for that month. The query selects using the Month([SurveyDate]) field and the Month(Now()) criteria and the same thing for the year. Sorry if I'm going into too much detail but I thought maybe these things might help.


How do I save each monthly report? My initial thought was to have a report for each month but I didn't really want to have hundreds of reports. Is it possible to save each invoice to a table?

I'd be grateful for any help that you can throw my way.

[Micron]

Your invoices should be records in their own table, probably linked by customer id. You would be able to repeatedly reproduce a replica of the report based on month, customer id or any other criteria you wish to apply. If you have a price/costs table to feed those numbers into your invoice records, that means you have to watch out how you use those numbers. One way is to store the costs/discounts in the invoice record rather than link to those numbers. If you link to the numbers and later increase them, those reports would reflect the new values, which would be historically inaccurate. Consider posting a pic of your table relationships if you've created them (you can't copy/paste images here so see How to attach files at the top of the page). Be prepared for questions though, since understanding the process is crucial to giving any design advice.

[pbaldy]

A couple of other thoughts. You want what's known as a parameter query. In your case, you'd enter a date or date range on a form, and the query would get it from there. You definitely don't want separate queries or reports for each month.

Secondly, using the Month() function might work now, but it will backfire next year when you have 2 years worth of data. You'll get this March's invoices plus last March's. Better to use a date range, which gives you more flexibility anyway.

[Kelev Gadol]

A couple of other thoughts. You want what's known as a parameter query. In your case, you'd enter a date or date range on a form, and the query would get it from there. You definitely don't want separate queries or reports for each month.

Secondly, using the Month() function might work now, but it will backfire next year when you have 2 years worth of data. You'll get this March's invoices plus last March's. Better to use a date range, which gives you more flexibility anyway.

Thanks for your response. I use the Month() function and the Year() function so repeating last year's data shouldn't happen. I'll keep the date range option in mind although I think the aforementioned functions are working ok so might be best left alone. I don't want to break anything!

[Kelev Gadol]

Your invoices should be records in their own table, probably linked by customer id. You would be able to repeatedly reproduce a replica of the report based on month, customer id or any other criteria you wish to apply. If you have a price/costs table to feed those numbers into your invoice records, that means you have to watch out how you use those numbers. One way is to store the costs/discounts in the invoice record rather than link to those numbers. If you link to the numbers and later increase them, those reports would reflect the new values, which would be historically inaccurate. Consider posting a pic of your table relationships if you've created them (you can't copy/paste images here so see How to attach files at the top of the page). Be prepared for questions though, since understanding the process is crucial to giving any design advice.

Exactly! I want to save each invoice in a table where each invoice has its own set of fields in a record. As you say, I don't want the data in the invoices to be taken from the prices table in case there are changes. I'll try to work out how to attach a picture of the table relationships.

[Welshgasman]

Exactly! I want to save each invoice in a table where each invoice has its own set of fields in a record. As you say, I don't want the data in the invoices to be taken from the prices table in case there are changes. I'll try to work out how to attach a picture of the table relationships.

That is easy. Take a picture and upload as a file. See top menu bar 'How to Attach Files'
DO NOT copy and paste into a post. Whilst it looks like you can, the pasted image is removed when you post the message. Attach images only.

[Kelev Gadol]

Your invoices should be records in their own table, probably linked by customer id. You would be able to repeatedly reproduce a replica of the report based on month, customer id or any other criteria you wish to apply. If you have a price/costs table to feed those numbers into your invoice records, that means you have to watch out how you use those numbers. One way is to store the costs/discounts in the invoice record rather than link to those numbers. If you link to the numbers and later increase them, those reports would reflect the new values, which would be historically inaccurate. Consider posting a pic of your table relationships if you've created them (you can't copy/paste images here so see How to attach files at the top of the page). Be prepared for questions though, since understanding the process is crucial to giving any design advice.

Exactly! I want to save each invoice in a table where each invoice has its own set of fields in a record. As you say, I don't want the data in the invoices to be taken from the prices table in case there are changes. I'll try to work out how to attach a picture of the table relationships.

[Micron]

You'd want tblInvoices.CompanyID related to tblCompanies.ID. Invoices table would have fields for inv number, date created, etc. IF you want itemized lines on the invoice you'd want tblInvoiceItems, relating InvoiceID to InvoiceItemsID and/or tblInvoiceNotes if there are to be a series of notes/comments. As for comments/notes, it is possible to put them into one memo field (long text) but it sometimes raises small issues regarding making sure the added notes are appended. Also, they are then just a mass of words that you can't easily attribute to an individual or date stamp if that's important. Those points would apply to your existing comments fields, which may not matter for you.

You may eventually find that ID is a rather useless name for a field. InvoiceIDpk and InvoiceIDfk tell you much more. Advise to not have spaces in your field names (nor special characters, not that you do - yet). Also, looks like you have multi value fields, which is not recommended.

[Kelev Gadol]

You'd want tblInvoices.CompanyID related to tblCompanies.ID. Invoices table would have fields for inv number, date created, etc. IF you want itemized lines on the invoice you'd want tblInvoiceItems, relating InvoiceID to InvoiceItemsID and/or tblInvoiceNotes if there are to be a series of notes/comments. As for comments/notes, it is possible to put them into one memo field (long text) but it sometimes raises small issues regarding making sure the added notes are appended. Also, they are then just a mass of words that you can't easily attribute to an individual or date stamp if that's important. Those points would apply to your existing comments fields, which may not matter for you.

You may eventually find that ID is a rather useless name for a field. InvoiceIDpk and InvoiceIDfk tell you much more. Advise to not have spaces in your field names (nor special characters, not that you do - yet). Also, looks like you have multi value fields, which is not recommended.

Thanks Micron.
I do have a mutlivalue field and I hadn't noticed until you just pointed it out. I will change that. Actually, I don't use that field so I will remove it. It doesn't matter to me and I doubt very much that it ever will.
Quick question, why IDpk and IDfk?

[Micron]

pk - primary key; fk - foreign key

[pbaldy]

Thanks for your response. I use the Month() function and the Year() function so repeating last year's data shouldn't happen. I'll keep the date range option in mind although I think the aforementioned functions are working ok so might be best left alone. I don't want to break anything!

I understand, though I'll add a warning. Using those functions on your fields makes the query non-sargable. With larger datasets you will see degraded performance, as the 2 functions have to be applied to every record in the table and indexes can't be used. More here:

https://en.wikipedia.org/wiki/Sargable

Edit: I'm not saying you should change it now, but keep it in mind if you data grows and you see poorer performance from this query.

[Kelev Gadol]

I understand, though I'll add a warning. Using those functions on your fields makes the query non-sargable. With larger datasets you will see degraded performance, as the 2 functions have to be applied to every record in the table and indexes can't be used. More here:

https://en.wikipedia.org/wiki/Sargable

Edit: I'm not saying you should change it now, but keep it in mind if you data grows and you see poorer performance from this query.

Thanks for that Paul. I doubt it will become a problem as it's only really used for monthly reports just for invoicing. I'll bear it in mind though. As I said in my original post, I'm quite new to databases and don't need to become an expert. I'd just like to keep a record of the invoices created which at the moment is done by keeping each invoice as a PDF.