|
| 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
September 3rd, 2007, 02:50 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
relationship between a table as a whole and a field in another table
How do I create a relationship where ALL the records in a particular
table have a relationship to 1 particular /unique record in another table? I want a relationship between a table's name (all records in it) and a field with the table's name in another table. Story: I have a customer table, now each customer has 3 types of bill templates. I made 3 tables, one for each type of bill template, with a autonumber customer ID related back to the customer table. For 2 types of bill templates, he can only have one bill (record) for that type of bill template; for the 3rd type of bill template table, each customer can have infinite amount of bill templates (0-~4 in real life). There is also a "my-business-specific info" table, with 1 record for each type of bill template. Each record in "my-business- specific info" table is different (rec wise, not field wise), but there are only 3 of them because there are only 3 types of bill templates. Remote possibility more types of bill templates will be added one day by me (the designer), not the user. The complete bill template is supposed to be assembled by combining a customer rec+one of the customer's bill templates recs+my business specific rec for that type of bill template. The bill template is then going to be printed and the items ordered and amount due written out by hand then mailed. There is no concept of products or orders or point in time data in this database. I haven't finished making the application yet, I am at table design stage. I am a first time Access user and first time anything databases, but I have done some amateur programing in C++, Javascript, HTML, and Perl. My VB experience is limited to 1 semester of VB Classic years ago in high school/secondary school, but I think I can figure VBA pretty quickly. Another way of making this relationship I thought of is making a "static field" in each bill template table. I thought up of a way of making a "static field" (a textbox type) by setting the field's default value to the table name, and then making a validation rule so you can't change that field (I guess I can later not include a way to see/change the "static field" from a form, but I want some protection against myself/datasheet view too). The "static field" would have a relationship with the "type of bill template" (listbox type I think) field in the "my-business-specific info" table. Is there a better way to do a "static field" than waste space on a column/field with every rec having the same value, like a 'field source = "name of table"'? I am trying to make a decent "normalization", if I combine the 3 types of bill template tables together into one big bill template table, there will be alot of empty fields for each rec, since most of the fields on one type of bill template don't exist on any other types of bill templates, violating normalization rules I think. But atleast I will have a useful field with "the type of bill template" info, rather than the "static field" solution above. Am I asking too much of Access or relational databases, or is this kind of relationship something usually later emulated (look up current record set's table name, find rec in other table with same value in field X???) by VB code in the form/report? 
|
|
#2
September 3rd, 2007, 03:30 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
relationship between a table as a whole and a field in another table
|
|
#3
September 3rd, 2007, 03:43 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
relationship between a table as a whole and a field in another table
On Sep 2, 10:30 pm, John W. Vinson
wrote: [cut] Would that work for you, or are these tables different in *structure*? If they are, you may need a one-to-one relationship for Subclassing. John W. Vinson [MVP] 75% different field/structure. I could make one table, but some fields will be empty for one record, while another record will have a different set of empty fields. Can you explain "subclassing" (google says its a database term for inheritance), and is it even possible in Access?
|
|
#5
September 3rd, 2007, 06:36 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
relationship between a table as a whole and a field in another table
|
|
#6
September 3rd, 2007, 12:16 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
relationship between a table as a whole and a field in another table
On 3 Sep, 05:48, Armen Stein wrote:
1 - single table with some of the fields not applicable, and therefore Null or 2 - one-to-one tables for each subtype. In your case, if 75% are common fields, then I would probably recommend the single table. I'd go with the subclassing approach (2). Best to avoid nullable columns IMO i.e. does NULL mean 'not applicable' or 'applicable but unknown' etc? Nulls really don't cost you anything You obviously never lost time due to newbie thinking that NULL = NULL evaluates to TRUE. Jamie. --
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
