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.
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.