Reducing the number of tables, updating, etc (1 Viewer)

A1ex037

Registered User.
Local time
Tomorrow, 00:52
Joined
Feb 10, 2018
Messages
50
First of all, I hope that all of you guys and girls are safe and together with your families. I do hope that we will all emerge from this corona thing soon.

Now that we are inside most of the time, I started to play with our sales database, trying to remember what was done, how it was made and what else can be done to improve it (so most likely I will be posting few threads in varios sections whenever I get stucked :)). My skills are at a beginner level, and I know that there are plenty of things that I can fix and improve. Unfortunately, half of the things was done on a live database, there are some redundant tables (I think), and I could really use a hand in order to sort this out.

There is a tblSales that handles SaleID, SaleDate, CustomerID and other general information regarding the particular sale.
The other table is called tblSalesProduct that handles all sold products (and it is related to tblSales).
The redundant part is tblInvoices that stores only two pieces of information: SaleID and InvoiceNo. Since every SaleID is related only to one InvoiceNo, is it safe to assume that I can transfer InvoiceNo from tblInvoices to tblSales, right? The other thing about the current tblInvoices is that I would like to be able to automatically generate Invoice numbers. They should start from 1 every new year. At this point it is used AutoNumber, but few times it messed things up (sale is cancelled and deleted, but the autonumber just adds another one, thus skipping one InvoiceNo). So far I figure that frmSales has to check if the last entered InvoiceNo in tblSales is still in the same year (year is displayed on the frmSales), if so add +1 and store it in tblSales, else set InvoiceNo to 1 (new year). Idea is to add new field to tblSales (called InvoiceNo), use a query to update that field with values from tblInvoices, remove the tblInvoices completely from database and then on a frmSales (when a New button is clicked), the from should check about the InvoiceNo, insert new InvoiceNo in the appropriate field and store it in tblSales.

I'd appreciate suggestions about this.
 

Micron

AWF VIP
Local time
Today, 19:52
Joined
Oct 20, 2018
Messages
3,476
Since every SaleID is related only to one InvoiceNo, is it safe to assume that I can transfer InvoiceNo from tblInvoices to tblSales, right?
Maybe, but this would mean that you cannot ever have more than one sale data value per invoice. If that fits your business model, then you might get away with it. However, I think it would mean that you cannot ever have more than one sales transaction per invoice and if that a situation ever arises where it doesn't fit the norm, you will have an issue. Without a deeper understanding of the nature of your business, I'm thinking that there would be nothing to be gained by removing this possibility by redesigning things.

On the issue of invoices, I've never understood the practice of being so anal about invoices or PO's - like they MUST follow some sort of sequential pattern and God forbid that we can find valid PO's/invoice numbers for 001 and 003 but not 002. Maybe in my next life I will be an accountant ;)

Be that as it may, autonumbers are ONLY for providing a unique identifier for a record and should never be used as meaningful data, so do not even consider involving them in anything else. If you want to institute a pattern for unique PO or invoice numbers, that is OK and is doable, but leave autonumber fields out of it, especially if you're going to be hung up about sequential numbering. I can see making the year value part of the identifier, but worrying about starting over at 1 next year - I'm not sure I grasp the importance of that.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:52
Joined
Feb 28, 2001
Messages
27,001
@Micron,

I agree with you on the incredibly anal retentive nature of business accountants, but I have an answer for you regarding consecutive invoice numbers. There has to be a contiguous relationship among invoice numbers because that is one of the validation rules they use to show that nothing has "fallen through the cracks." Which is why the autonumber can never be used for an invoice number when that kind of validity checking is in force. It is basically an auditing tool to verify that nobody is hiding invoices and skimming profits. And at that point, since it suddenly has meaning, it can no longer be a simple autonumber.

@A1ex037,

There are articles in this forum regarding generation of custom (multi-component) sequence numbers. Look up "Custom Numbering" and "Custom Autonumber" as topics within this forum's SEARCH facility. They will show you how to deal with a number that resets every fiscal or calendar year.
 

Micron

AWF VIP
Local time
Today, 19:52
Joined
Oct 20, 2018
Messages
3,476
Thanks for the explanation, Doc_Man. I'll buy it, but I have trouble imagining how I'd get away with it. I'm too dumb (or is it honest?) to understand how I could invoice a company using my company's invoice and get that company to send a cheque directly to me and payable to me - especially since that would be a break from the norm. If you know how that can be done, you obviously should not reveal that here so no worries. I would think it would require an accomplice at the other end - someone stupid enough to think that no one would ever spot a cheque made out to me that matches an invoice on file at the paying company.
 

A1ex037

Registered User.
Local time
Tomorrow, 00:52
Joined
Feb 10, 2018
Messages
50
Thank you for your responses. Both of you have made valid and solid arguments.

@Micron
I don't have a problem with storing InvoiceNo directly into tblSales. It cannot create any problem in work. tblSales and tblSalesProduct are both used when customer is purchasing, so I can add or delete as many products as I can. Sold products are stored inside tblsSalesProduct (with SaleID that corespondent to SaleID in tblSales). My question was more intended as "is this a right way to do", considering normalization, etc. You are right about numbering and invoices, but that is something that I cannot change. DocMan has explained it simply and efficiently. It is the rule we all have to obey to. I do have a problem how to implement that (my mistake about AutoNumber, I was thinking of incremental numbering).

@DocMan
Thank you for your suggestion. I am already on it. That is exactly what I need (reset numbering every calendar year).
 

mdnuts

Registered User.
Local time
Today, 19:52
Joined
May 28, 2014
Messages
128
There has to be a contiguous relationship among invoice numbers because that is one of the validation rules they use to show that nothing has "fallen through the cracks." .

Very true - and law as far as VAT is concerned. Probably doesn't apply in this, but just an example.

Personally, I shiver at the thought of allowing - nah, making it Law to follow a predicable numbering scheme when it comes to doing business.

@Micron just make sure nothing refers to that table, if it does, alter accordingly of course.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:52
Joined
Feb 28, 2001
Messages
27,001
@mdnuts - thanks for the amplification. In the USA, it is not a matter of law - but when working with the U.S. Government, it is an auditing requirement that all transactions have contiguous numbering. And while it is not law, that requirement contributes to your system's "vulnerability/readiness" score on the annual review of every system. Trust me, you DON'T want to be on the wrong end of a U.S. Navy audit. If you thought Agent K had no sense of humor in Men In Black, he is positively hilarious compared to a Navy auditor.
 

mdnuts

Registered User.
Local time
Today, 19:52
Joined
May 28, 2014
Messages
128
@The_Doc_Man I'm familiar with various Fed audits - although not of the accounting nature. And in my past I've had to uncover/prove theft which I largely did based on invoices. I get it but to me it just seems like an information security gap. Granted not as bad as having a predictable order numbering scheme but still any predictability isn't good.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:52
Joined
Feb 28, 2001
Messages
27,001
I'm on your side, but I was addressing the original question in (I guess) an oblique way. We always use the mantra that to use a "true" autonumber, the number CANNOT have any meaning. Since the OP's question relates to a meaningful sequence number, autonumber would not be appropriate for the given situation.
 

A1ex037

Registered User.
Local time
Tomorrow, 00:52
Joined
Feb 10, 2018
Messages
50
Since the OP's question relates to a meaningful sequence number, autonumber would not be appropriate for the given situation.
Very true. Maybe I mentioned that a bunch of stuff I had to do live, since time was an issue. I followed your first answer, found several links, and now it is already working (never thought of Dmax). Thank you again.
 

Users who are viewing this thread

Top Bottom