How to unified same tables for 3 different MS Access file

[blu_macey]

Hello Everyone!

I hope this is the right thread I'm posting.

Scenario

I have 3 sets of MS Access File (NEDA, RDC,& Summid) built with the same sets of structures. Originally at MS Access 2007, but now I'm using the built in 2010 version for Lenovo Flex 2.
The table consist of:
*Chart of Accounts
*Journal Entry Voucher Master File
*Journal Entry Number Master File
*Supplier Master File
Queries are the same (links, designed, etc...)
Forms are also the same (links, designed, etc...)
Reports are also the same (links, designed, etc...)

Problem

The difference for these three file is the Journal Entry Number because each file requires a different JEV format
Example:
For NEDA it should be NEDA-Year-Month-Number Series
For RDC it should be RDC-Year-Month-Number Seried
Laslty for summid it should be Summid-Year-month-Number Series
and separate transaction listing for the purpose of calling data for the report, such as List of disbursements and trial balance.

But for these three MS Access file, Chart of Accounts and Supplier Master File do have the same set of data. That's why whenever a data is not available I am encoding the said information in each MS Access file (inputting the data three times).
Example:
If an asset account such as Office Supplies is not available, I will input in the Chart of Account Form for NEDA, then RDC, then Summid.

Question

Is there a way where I can unified the Chart of Accounts and Supplier Master File into one but still can entry/edit the same in each MS Access forms. Also can still link to each queries, tables,and reports?
So that I will encode data one time only and save space.


Any comment, suggestion, violent reaction and Ideas are very much welcome.

Hoping to hear from you soon.

Thank you,
Chino

 

[Solo712]

Hello Everyone!

I hope this is the right thread I'm posting.

Scenario

I have 3 sets of MS Access File (NEDA, RDC,& Summid) built with the same sets of structures. Originally at MS Access 2007, but now I'm using the built in 2010 version for Lenovo Flex 2.
The table consist of:
*Chart of Accounts
*Journal Entry Voucher Master File
*Journal Entry Number Master File
*Supplier Master File
Queries are the same (links, designed, etc...)
Forms are also the same (links, designed, etc...)
Reports are also the same (links, designed, etc...)

Problem

The difference for these three file is the Journal Entry Number because each file requires a different JEV format
Example:
For NEDA it should be NEDA-Year-Month-Number Series
For RDC it should be RDC-Year-Month-Number Seried
Laslty for summid it should be Summid-Year-month-Number Series
and separate transaction listing for the purpose of calling data for the report, such as List of disbursements and trial balance.

But for these three MS Access file, Chart of Accounts and Supplier Master File do have the same set of data. That's why whenever a data is not available I am encoding the said information in each MS Access file (inputting the data three times).
Example:
If an asset account such as Office Supplies is not available, I will input in the Chart of Account Form for NEDA, then RDC, then Summid.

Question

Is there a way where I can unified the Chart of Accounts and Supplier Master File into one but still can entry/edit the same in each MS Access forms. Also can still link to each queries, tables,and reports?
So that I will encode data one time only and save space.


Any comment, suggestion, violent reaction and Ideas are very much welcome.

Hoping to hear from you soon.

Thank you,
Chino

Hi Chino,
it looks like you have quire a bit of redundancy there. If the only dfference between the three Access files / databases is a structure of one table (field ?) you should only need in a single database. There is a way to structure the difference in the journal encoding by linking the transactions to their correct JEN format, or even suspend the link until it is known which of the three formats is referenced.


Best,
Jiri

 

[blu_macey]

Hello Jiri,

Thanks a lot i'll work on your idea over the holiday here in the Philippines
originally I have combined all of these 3 files into one but got some difficulty in mother and child links. it appears that per table it only recognize one link. i.e. if I use the Chart of account table and link it to NEDA Jev master file I won't be able to use it in another JEV master file which is very much needed for relationship fpr queries.

I've got your point and will grab your Idea over the Easter /Lenten Holiday

Thank you very much,
Chino

 

[Solo712]

Hello Jiri,

Thanks a lot i'll work on your idea over the holiday here in the Philippines
originally I have combined all of these 3 files into one but got some difficulty in mother and child links. it appears that per table it only recognize one link. i.e. if I use the Chart of account table and link it to NEDA Jev master file I won't be able to use it in another JEV master file which is very much needed for relationship fpr queries.

I've got your point and will grab your Idea over the Easter /Lenten Holiday

Thank you very much,
Chino

You are welcome. If you want to send the structure of the tables in your three Access databases after the holidays, I am sure I can help to get you on the right track.

Best,
Jiri