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.

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" <(E-Mail Removed)> wrote in message
news:5D9D4A8E-4070-4516-88B2-(E-Mail Removed)...
> 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.
>

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.


"vanderghast" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:5D9D4A8E-4070-4516-88B2-(E-Mail Removed)...
> > 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.
> >
>
> .
>