Duplicate record with related records (1 Viewer)

capsula4 · Feb 22, 2008

Hello!

I have a 1-many relationship:

tblCOMPANY (one) - tblBANKS (many)

I have a main form called COMPANY with a link to a subform (opens in new window) of BANKS.

I added the wizard button to duplicate record COMPANY but it doesn't duplicate the related BANKS that COMPANY has.

I've noticed that duplicate record button only duplicates one simple record, and I would want it to duplicate all the associated records to one main record, how can this be possible?

RuralGuy · Feb 23, 2008

I believe you have not received a response because what you are trying to accomplish is a violation of a normalized db. Maybe if you explain the business principle you wish to impliment then we could offer suggestions.

capsula4 · Feb 25, 2008

What I'm doing with this DB is storing the available fund of dif companies on dif banks, each day.

Each Company can operate with many Banks

What I have:

tbleDays: ID, DAY
tbleFunds: CompanyBank_ID, Value
tlkpCompBank: CompanyBank_ID, Company_ID, Bank_ID
tlkpComp: Company_ID, Company
tlkpBank: Bank_ID, Bank

So I select a DAY, then a CompanyBank_ID and insert a value for that day.

tlkpBank stores all the availables Banks
tlkpComp stores all the availables Companies
and tlkpCompBank stores the diff Banks that operates with each Company.

Here are the relationships I have done:
One day -> Many Companies
One Company -> Many Banks

The problem here is: each day should have all the stored CompanyBanks_ID up to date. So I have to be selectin all the availables "CompanyBanks_ID" in each day.

Any suggestion?

RuralGuy · Feb 26, 2008

I haven't answered because I was trying to understand you problem. Are you saying that you are trying to add a record for every bank for every company every day and some of these records will have something other than $0 as a value? Sorry but my feeble brain is having trouble understanding this.

capsula4 · Feb 27, 2008

RuralGuy said:
you are trying to add a record for every bank for every company every day and some of these records will have something other than $0 as a value? Sorry but my feeble brain is having trouble understanding this.

Yeah, its kinda of that.

Every day has to be filled with every company on the DB and every related bank of that company in the DB, and not necessary 0 as value.

Here is my current relationship window:

On my form I have to select all the available companies with all the available banks per company (i have done a combobox to sort the relationships, a company field to filter the bank field, which actually is the REL_ID field), but I think this can be done in a better way so as to avoid enterin all the time all the banks per company.

RuralGuy · Feb 27, 2008

Why is it necessary to have records in your tables that have $0 value? You are wasting a lot of disk space for what seems to be no valid reason.

capsula4 · Feb 28, 2008

RuralGuy said:
Why is it necessary to have records in your tables that have $0 value? You are wasting a lot of disk space for what seems to be no valid reason.

Well, actually the way the DB is organized, I'm have the possibility to avoid storing a 0 values.

Anyway, I don't have any 0 value on any REL_ID, so that's why I want to have a way to have automatically all the available banks per companies for each day. For example, I create a new day and on a form appears sth like this:

CompanyName1 - Bank1: [MONEY AVAILABLE]
CompanyName1 - Bank2: [MONEY AVAILABLE]
CompanyName1 - Bank3: [MONEY AVAILABLE]
CompanyName2 - Bank1: [MONEY AVAILABLE]
CompanyName3 - Bank1: [MONEY AVAILABLE]
... (and so on)

Showing all the available companies and banks, since all of em are being filled with a Money Amount different from 0.

Not sure if there is a way to make this. I thought of creating a tbl and making each BANK a column, but in that case, I would be storin many 0 values when I create a new BANK.

RuralGuy · Feb 28, 2008

I was hoping someone else would jump in here since I am having so much trouble getting my arms around your db concept. I'm afraid I will have to bow out of the thread at this point. I'm not any help to you at all. Sorry.

capsula4 · Feb 28, 2008

RuralGuy said:
I was hoping someone else would jump in here since I am having so much trouble getting my arms around your db concept. I'm afraid I will have to bow out of the thread at this point. I'm not any help to you at all. Sorry.

Well, anyway, thanks a lot Allan for trying to help me! I don't really know any way to make it work...

I thought of probably using the default value option and make it incremental somehow as you go on putting records... do you get what I'm sayin here?

CraigDolphin · Feb 28, 2008

Capsula,

like RG I find it hard to interpret what you're trying to do. So, have a look at the attached db and see if it gives you some ideas.

Essentially it's a quick db designed to track bank balances for companies, by date. It needs a bit of work to make it easier to enter dates (calendar date picker etc) but it provides the general gist of table structure and how you might use forms to view/edit the data.

Hope it's helpful.

capsula4 · Feb 29, 2008

Thanks a lot CraigDolphin!

What I'm trying to do is exactly the same as what that DB does, but I have sorted it out differently, organizing it principally by DAY instead of COMPANIES since the balances should be completed daily on the company I'm working.

The problem with my DB and the one you posted is that you have to choose the bank through a combobox and I would want it to display all available banks automatically (like creating registries automatically).

Since I guess that creating registries automatically is pretty hard, I thought that maybe it was possible to achieve something similar by using "Default Value" option from forms and make it like autoincremental so on the first entry of banks it would appear the first bank on the combo, and in the next entry would appear the second bank.

Another idea I have is that once you enter a bank, take it out from the list of the combobox, possibly through a query?

Note: I know this ideas wouldn't work on the bank.db sample since there you gotta pick the day. Im attaching my DB in case it helps but its in spanish.
1. Company, 2. Day, 3. Load Balances, 4. Additional Comment, 5. Differences of Balances (TODAY - YESTERDAY) just for checking and having a control, 6. View Report.

capsula4 · Feb 29, 2008

I created another topic about my third idea:
http://www.access-programmers.co.uk/forums/showthread.php?p=678076

Anyway, if any of u got any idea for the "default value" thingie or create those records automatically would be nice... maybe with some VBA, what do you think?

CraigDolphin · Feb 29, 2008

Capsula,

in the db I posted you only ever have to choose the bank once for each company (the first time you add the bank to a company). Thereafter, all banks with a relationship to the company will show up automatically with the latest balance that has been entered.

Why you would want to show banks that have no actual relationship to the company is very odd to me. But, it would a be a simple matter to write an insert query to add a record in the CompanyBankRelationship table for each Bank in the db at the time you create a new company record.

Still, whatever floats your boat.

capsula4 · Feb 29, 2008

CraigDolphin said:
in the db I posted you only ever have to choose the bank once for each company (the first time you add the bank to a company). Thereafter, all banks with a relationship to the company will show up automatically with the latest balance that has been entered.

Sorry! I missunderstood the proccess of that DB, now I get and think I got a new idea on how to sort it out.

CraigDolphin said:
Why you would want to show banks that have no actual relationship to the company is very odd to me.

Actually I don't wanna do that, the fact is that I sorted my db differently. You have to create the relationship Company-Bank and then, daily, you also have to be chosing all the available banks per company, pretty weird now that I realise.

CraigDolphin said:
But, it would a be a simple matter to write an insert query to add a record in the CompanyBankRelationship table for each Bank in the db at the time you create a new company record.

May I be able to make this insert query that each time I create a new day, it adds records for each Relationship?

Like i click "New Day" and it creates somethin like this:

DAY ID | RELATIONSHIP
1, 1
1, 2
1, 3
1, 4
1, ... until last relationship ID

If that can be possible you have saved my DB.

Duplicate record with related records (1 Viewer)

capsula4

Registered User.

RuralGuy

AWF VIP

capsula4

Registered User.

RuralGuy

AWF VIP

capsula4

Registered User.

RuralGuy

AWF VIP

capsula4

Registered User.

RuralGuy

AWF VIP

capsula4

Registered User.

CraigDolphin

GrumpyOldMan in Training

Attachments

capsula4

Registered User.

Attachments

capsula4

Registered User.

CraigDolphin

GrumpyOldMan in Training

capsula4

Registered User.

Similar threads

Users who are viewing this thread