If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design Issue
Creating a invoice tracking DB yet there is no standardization from the 7
different formats that customers send in. Some fields are always on invoices such as number ,date, description, but there are others that have additional fields that need to be recorded. Should i be creating as many fields to match all invoices fields from different customers? There will certainly be null values in some fields while others contain data. Thanks |
#2
|
|||
|
|||
Design Issue
I think only you can look at how many invoice form types you have and how
varied they are to decide this. I might see if it would be feasible to put some generic fields to be used on different forms. Or, if there aren't that many different forms, you might have fields become visible or not visible based on some entered criteria that tells what form you are working with. Why does it really matter if some fields are null? Bonnie http://www.dataplus-svc.com acss wrote: Creating a invoice tracking DB yet there is no standardization from the 7 different formats that customers send in. Some fields are always on invoices such as number ,date, description, but there are others that have additional fields that need to be recorded. Should i be creating as many fields to match all invoices fields from different customers? There will certainly be null values in some fields while others contain data. Thanks -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Design Issue
It does not matter is some fields are null yet i wanted to get it right as
far as design.In fact when i create the forms for end users, I will have to dedicate one form per customer dependant on the fields needed for them. It should be 7 forms called out by the switchboard with a button dedicated to each form. Just trying to keep it simple but in a case where invoices have different fields, i guess this is my only choice. "bhicks11 via AccessMonster.com" wrote: I think only you can look at how many invoice form types you have and how varied they are to decide this. I might see if it would be feasible to put some generic fields to be used on different forms. Or, if there aren't that many different forms, you might have fields become visible or not visible based on some entered criteria that tells what form you are working with. Why does it really matter if some fields are null? Bonnie http://www.dataplus-svc.com acss wrote: Creating a invoice tracking DB yet there is no standardization from the 7 different formats that customers send in. Some fields are always on invoices such as number ,date, description, but there are others that have additional fields that need to be recorded. Should i be creating as many fields to match all invoices fields from different customers? There will certainly be null values in some fields while others contain data. Thanks -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Design Issue
well, the danger in that kind of design is always that if you encounter an
8th format (a new customer, perhaps) then you have to add additional fields to the tables and redesign queries, forms, reports, rewrite code... what is some of this additional data that must be recorded? how is it used by your company? more info, please. hth "acss" wrote in message ... Creating a invoice tracking DB yet there is no standardization from the 7 different formats that customers send in. Some fields are always on invoices such as number ,date, description, but there are others that have additional fields that need to be recorded. Should i be creating as many fields to match all invoices fields from different customers? There will certainly be null values in some fields while others contain data. Thanks |
#5
|
|||
|
|||
Design Issue
The additional fields that some invoices have that others do not are just for
reference in tracking the actual order. Some can have one reference while others can have several.In reading postings i can see it will not have a material impact since as long as there are at least two reference fields available. I just wanted to understand better in having null values in records dependant on customer. Thanks "tina" wrote: well, the danger in that kind of design is always that if you encounter an 8th format (a new customer, perhaps) then you have to add additional fields to the tables and redesign queries, forms, reports, rewrite code... what is some of this additional data that must be recorded? how is it used by your company? more info, please. hth "acss" wrote in message ... Creating a invoice tracking DB yet there is no standardization from the 7 different formats that customers send in. Some fields are always on invoices such as number ,date, description, but there are others that have additional fields that need to be recorded. Should i be creating as many fields to match all invoices fields from different customers? There will certainly be null values in some fields while others contain data. Thanks |
#6
|
|||
|
|||
Design Issue
well, you could consider that "miscellaneous additional data". i might
handle that by creating a table that lists the descriptions of all the misc data you might want to store for any invoice (these would be the names you might otherwise have given to individual fields in the table, to identify the data). if necessary, you could go so far as to associate each description with a specific customer. then add a data table as the -many side of a one-to-many relationship with the invoices table. in that table you can enter as many "additional data" records as you need for each individual invoice record. the setup/relationships would follow this example, as tblInvoices InvoiceID (primary key) InvoiceNumber InvoiceDate Company tblReferences RefID (pk) RefName tblInvoiceReferences InvRefID (pk) InvoiceID (foreign key from tblInvoices) RefID (foreign key from tblReferences) RefValue (whatever the actual piece of data is) tblInvoices.InvoiceID 1:n tblInvoiceReferences.InvoiceID tblReferences.RefID 1:n tblInvoiceReferences.RefID enter the data in a standard mainform/subform layout, where the mainform is bound to tblInvoices, the subform is bound to tblInvoiceReferences, and there is a combobox control on the subform bound to the RefID field in tblInvoiceReferences, with its' RowSource set to tblReferences. hth "acss" wrote in message ... The additional fields that some invoices have that others do not are just for reference in tracking the actual order. Some can have one reference while others can have several.In reading postings i can see it will not have a material impact since as long as there are at least two reference fields available. I just wanted to understand better in having null values in records dependant on customer. Thanks "tina" wrote: well, the danger in that kind of design is always that if you encounter an 8th format (a new customer, perhaps) then you have to add additional fields to the tables and redesign queries, forms, reports, rewrite code... what is some of this additional data that must be recorded? how is it used by your company? more info, please. hth "acss" wrote in message ... Creating a invoice tracking DB yet there is no standardization from the 7 different formats that customers send in. Some fields are always on invoices such as number ,date, description, but there are others that have additional fields that need to be recorded. Should i be creating as many fields to match all invoices fields from different customers? There will certainly be null values in some fields while others contain data. Thanks |
#7
|
|||
|
|||
Design Issue
acss wrote:
The additional fields that some invoices have that others do not are just for reference in tracking the actual order. Some can have one reference while others can have several.In reading postings i can see it will not have a material impact since as long as there are at least two reference fields available. I just wanted to understand better in having null values in records dependant on customer. Having extra fields null isn't a problem. Go for it. I do that all the time. But also consider adding a comments field so the users can key in stuff that simply doesn't belong in any of the predefined fields. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
Thread Tools | |
Display Modes | |
|
|