B
bulk88
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?
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?