designing form for inserting records into separate table

[Masoud]

Hello

I want to design my database for inserting records in 2 separate tables and
after make search event for searching in both tables and retrieve records.
Table 1 contains these fields:

Proj id: 2042
Field code: COM
Discipline: MG
Title: test 1
Doc Type: PR
Serial: 0002
Doc No: concatenated by Proj id+"-"+ Field code+"-"+ Discipline+"-"+ Doc
Type+"-"+ Serial and result should be some thing: 2042-COM-MG-PR-0002
Value of Doc No has been made by coding when inserting records in the form.

Table 2 contains these fields:

Projid: 2042
Doc TypeW
Unit: 114
Title:
Material Code: 1431
Serial: 0002
Doc No: Doc Type+"-"+ Projid+"-"+ Unit+"-"+ Material Code+"-"+ Serial and
result should be some thing like: DW-2042-114-1431-0002
Value of Doc No has been made by coding when inserting records in the form.
Now I want to make a form for inserting these records in the tables, how I
can do it? I mean with 1 form we can do it?
Also for searching in 2 tables and retrieving records into 1 form what I
have to do?
Thanks in advanced.

 

[vanderghast]

You have no much way to modify supplied tools, but can probably change your
own table design. and that is what I would try, ie, make one table for what
is common (and probably what you search will be in that common section). As
example, you can have that an electricity bill is not the same as, say, a
water (aqueduct) bill, and think about making two tables (one for each
type), but it may be more preferable to make THREE tables: one for what is
common to all types of bills, and, in a one to one relation to that 'common'
table, define a table for each subtype of bill, where the particular values
for each kind of bill would be held. So, your search form can easily live
bound to the table in 'common', and display result on a sub-form which can
change accordingly to the bill-type the main form has in focus (ie, you
define an electricity bill sub form, and a water bill subform).

It may not be appropriate to your case, but definitively, the ACTUAL design
you proposed sounds foreign to what the standard tools, delivered with a
database, 'are expecting to deal with'. I may have misread your question,
though.


Vanderghast, Access MVP

 

[Masoud]

Hi,

Thanks for your answer

Now I like to know how I can make third table automatically for common
fields, we have already table1, table2 and each of them have around 20000
records, and every day the records will increase, I have to use union query?
Or there is a better solution for this case?
Thanks in advanced.

 

[PieterLinden via AccessMonster.com]

Hi,

Thanks for your answer

Now I like to know how I can make third table automatically for common
fields, we have already table1, table2 and each of them have around 20000
records, and every day the records will increase, I have to use union query?
Or there is a better solution for this case?
Thanks in advanced.

Back up a couple of steps. Why can you not lump all the data into a single
table? Are you subclassing your entitites or something? If you are, you
should read Rebecca Riordan's article on Access Web, which is here:
http://www.mvps.org/access/tables/tbl0013.htm


The performance of union queries is ABYSMAL because it cant use the indexing
set up on any of the tables. I would do this only as a last resort.

If you subclass, you have the advantage of being able to use the two tables'
indexing to get optimal querying speed.