Four tables into one choose menu

[Dennis]

try to make a database for receipts.
Its almost finished, but I still have a problem.
I got four tables what need to function as a source. Thay are:
contact, company, internet and book. And maybe later five, but this
isn't necessary so long I know.
Then I got a form. This form needs one dropdown menu to choose one of
those sources. But the source got all a autonr as key and I'm afraid
that this numbers are the same whit the other source. So the table
that collect all the information(, like ingredient's etc.) go to two
source by one number.

Maybe I got some Ideas , but like to know also your opinion.
1. Make on table for all sources and make some thing blanc. (where I'm
afraid of is that it writes another information by the old
information.)
2. Make four table's and put them into one table whit a new ID, but I
think it collect then all the information until it is full to get an
ID.
3.Make four table's and join them whit a query.
4. Or make four table and use the source naam as ID and call this up
when you fill something in.

All of the source I make a new form. So I can put information in it
about the a new source.

What do you think it is easy to use and can easily be updated.

Think You very much for your help.
Greets Dennis

PS when it is finished I post it over here.

 

[i_takeuti]

try to make a database for receipts.
Its almost finished, but I still have a problem.
I got four tables what need to function as a source. Thay are:
contact, company, internet and book. And maybe later five, but this
isn't necessary so long I know.
Then I got a form. This form needs one dropdown menu to choose one of
those sources. But the source got all a autonr as key and I'm afraid
that this numbers are the same whit the other source. So the table
that collect all the information(, like ingredient's etc.) go to two
source by one number.

Maybe I got some Ideas , but like to know also your opinion.
1. Make on table for all sources and make some thing blanc. (where I'm
afraid of is that it writes another information by the old
information.)
2. Make four table's and put them into one table whit a new ID, but I
think it collect then all the information until it is full to get an
ID.
3.Make four table's and join them whit a query.
4. Or make four table and use the source naam as ID and call this up
when you fill something in.

All of the source I make a new form. So I can put information in it
about the a new source.

What do you think it is easy to use and can easily be updated.

Think You very much for your help.
Greets Dennis

PS when it is finished I post it over here.

 

[Marco Pagliero]

try to make a database for receipts.
Its almost finished, but I still have a problem.
I got four tables what need to function as a source. Thay are:
contact, company, internet and book.

I understand that for every receipt you use only one of the four
sources. Is it so?

And maybe later five, but this
isn't necessary so long I know.
Then I got a form. This form needs one dropdown menu to choose one of
those sources. But the source got all a autonr as key and I'm afraid
that this numbers are the same whit the other source. So the table
that collect all the information(, like ingredient's etc.) go to two
source by one number.

That's sure. You must store in every record of the receipt table
_which_ source was used, not only the key from the single source.

Maybe I got some Ideas, but like to know also your opinion.
1. Make on table for all sources and make some thing blanc. (where I'm
afraid of is that it writes another information by the old
information.)
2. Make four table's and put them into one table whit a new ID, but I
think it collect then all the information until it is full to get an
ID.
3.Make four table's and join them whit a query.
4. Or make four table and use the source naam as ID and call this up
when you fill something in.

All of the source I make a new form. So I can put information in it
about the a new source.

What do you think it is easy to use and can easily be updated.

It seems that in the source records some fields are the same for all
the four of them and some are not.
So I would make one table for the four sources with all the fields,
then let blank the fields that are not relevant for the specific
source. But every record must have a marker field: maybe 1 for
contact, 2 for company, 3 for internet and 4 for book. Or A, B, C and
D.

This way would be easy to make four queries, each onr giving you only
the records of one source, and make four forms, each one letting you
edit and update one of the sources. This form should set the marker
field appropriately for new records.

Then maybe I would make a query with only the fields that belong to
all sources and use this query to make receipts. So people has not to
choose the source, (but sees in the dropdown which record belongs to
which source)

If it is easier for you to work with four separate tables, than the
quick and dirty way is to put an extra field in each table and fill it
with '1' for contact, '2' for company and so on, then concatenate this
marker field together with the autoNr key and store this in the
receipt table.

Greetings
Marco P

 

[John W. Vinson]

try to make a database for receipts.
Its almost finished, but I still have a problem.
I got four tables what need to function as a source. Thay are:
contact, company, internet and book. And maybe later five, but this
isn't necessary so long I know.
Then I got a form. This form needs one dropdown menu to choose one of
those sources. But the source got all a autonr as key and I'm afraid
that this numbers are the same whit the other source. So the table
that collect all the information(, like ingredient's etc.) go to two
source by one number.

Maybe I got some Ideas , but like to know also your opinion.
1. Make on table for all sources and make some thing blanc. (where I'm
afraid of is that it writes another information by the old
information.)
2. Make four table's and put them into one table whit a new ID, but I
think it collect then all the information until it is full to get an
ID.
3.Make four table's and join them whit a query.
4. Or make four table and use the source naam as ID and call this up
when you fill something in.

By all means, use one table rather than four. Add a field to indicate the
source (contact, company, internet, book, ... etc.).

If you have four different series of autonumbers, just add a new autonumber
field to your new unified table, and run Append queries to migrate the data,
NOT including the autonumber.

If these four tables are related to *other* tables, and the autonumber values
have been propagated as foreign keys in these other tables, you will need to
do some update queries to correct the links.

John W. Vinson [MVP]

 

[Dennis]

Thanks a lot
I know now how to make it still be simple.
But you say make for each group a letter. Is there a way that you put
into a formule automatic a letter to that form.
let me explain a little better.
After this I make four forms.
Those forms I use to put in a new source.
But i I make a new contact is there a way that it get the letter A
right away. And for company letter B etc.
Whit out seeing anything on the form.

Thanks a lot.
The comments helped a lot.

 

[Marco Pagliero]

Thanks a lot
I know now how to make it still be simple.
But you say make for each group a letter. Is there a way that you put
into a formule automatic a letter to that form.
let me explain a little better.
After this I make four forms.
Those forms I use to put in a new source.
But i I make a new contact is there a way that it get the letter A
right away. And for company letter B etc.
With out seeing anything on the form.

I would put in the _BeforeUpdate event of the contact form

Sub Form_BeforeUpdate(Cancel as integer)
[Marker] = "A"
End Sub

And for the company, internet and book forms of course "B", "C", and
"D" respectively.

It will do it every time you change something in the form, but who
cares.

Greetings

Marco P