Tables and VBA (1 Viewer)

DevAccess

Registered User.
Local time
Today, 07:13
Joined
Jun 27, 2016
Messages
321
I have two different datasheet view/tables called wholesalers and retailers [part of subforms] on the form.

Now I have one tables which is combination of front end tables [as said above ]
when user enters data in the wholesalers datasheet view of the form it should do entry in the supplier table as suppliertype =wholesalers and same for retailers table when user enters the value in the retailers datasheet which is part of main form it should do entry in the supplier table as supplier type = retailers and all other fields value into it

Please assist me how to achieve this.
 

Ranman256

Well-known member
Local time
Today, 10:13
Joined
Apr 9, 2015
Messages
4,339
since you have 1 table,tSupplier, and a flag either: wholesaler or Retailer.
then display on the form the 2 queries
qsWholesalers, wholesalers only
qsRetailers, retailers only

why have 3 tables of suppliers?
 

MarkK

bit cruncher
Local time
Today, 07:13
Joined
Mar 17, 2004
Messages
8,178
I would have one table tCompany, with fields like IsWholesaler, IsRetailer, IsSupplier, and so on.
Mark
 

DevAccess

Registered User.
Local time
Today, 07:13
Joined
Jun 27, 2016
Messages
321
since you have 1 table,tSupplier, and a flag either: wholesaler or Retailer.
then display on the form the 2 queries
qsWholesalers, wholesalers only
qsRetailers, retailers only

why have 3 tables of suppliers?

I did not quite understand what do you mean.

I have three tables called tbl_wholesalers, tbl_retailers and tbl_suppliers.

These three tables are having identical columns except tbl_supplier will have supplier type as a column.

tbl_wholesalers and tbl_retailers are part of subform and display in form as datasheet view.

what I want is that whenever there is a entry in the either table ( tbl_wholesalers or tbl_retailers ) it should do the entry in the backend table tbl_suppliers as supplier type as wholesaler or retailer and with rest of data.

Please help me with this.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:13
Joined
Jan 20, 2009
Messages
12,849
These three tables are having identical columns except tbl_supplier will have supplier type as a column.

tbl_wholesalers and tbl_retailers are part of subform and display in form as datasheet view.

The three tables should be one, let's call it Contacts.

Add to this a system that flags the Contacts records to indicate they are Wholesaler, Retailer and/or Supplier. MarkK has suggested Boolean fields for IsRetailer etc. This is simple and easily handled on the forms. One record can be flagged for all three by ticking three boxes.

The step beyond this is the many-to-many system where a junction table stores the ContactID along with ContactTypeID which relates to a table of ContactTypes. This table should have a composite index to ensure that a Contact cannot have more than one setting for a single ContactType.

This system allows for an arbitrary number of attributes to be added to a Contact with new attributes simply added by the user in the ContactType table.

Just the presence of a record is sufficient to indicate that the attribute is available for the entity. A Boolean field might further indicate Active for example, or use a Date field to indicate the date the record was entered.

If we care about the history when the entity joined or left we can add fields to junction record with a system flagging a record as the current state. This system can extended to support more than just the StartDate and EndDate.

But again we have the choice to that allows us to store an arbitrary number of states for an arbitrary number of attributes. The developer has the choice to keep records about the history of records. It is the nature of the relational model. Anything is possible.

The developer's job is to build the database that utilises the relational model to the appropriate extent. A subscription system where the specification might require that a subscriber's history be retained needs more than a Boolean on Subscribe field in the client table. Such systems are also used to store an arbitrary number of phones or email addresses against a contact.

BTW This structure is very searchable because all the values are held in the same field. Ideal for holding large numbers of different attributes.

The disadvantage is that the values must always be held as the lowest common datatype, often as text. However this can be overcome by using a set of fields to support numbers, dates etc so the record can store the value as the correct datatype. Though even more complex to build, it improves searchability by avoiding the numeric fields if the search term is a string.
 

Users who are viewing this thread

Top Bottom