How to add to a table with a multiple indexed PK?

[MNNovice]

qryGrantFund is linked to frmAP so that when a fund is entered the field for GrantNo is automatically populated. This is the SQL for this query

SELECT tblGrantFunds.GrantFundID, tblFunds.FundNo, tblGrants.GrantNo, tblFunds.FundID, tblGrants.GrantID
FROM tblGrants RIGHT JOIN (tblFunds RIGHT JOIN tblGrantFunds ON tblFunds.FundID = tblGrantFunds.FundID) ON tblGrants.GrantID = tblGrantFunds.GrandID
ORDER BY tblFunds.FundNo;

I would like to know if it is possible to

1. Add two more fields to tblGrantFunds these are: ProjectNo (from tblProjects) and another field called SplitRatio. This is to show Federal Ratio part of each project within a grant. Is it possible to select the related project and its split ratio while entering AP Data using frmAP?

2. If yes, what changes will have to be made to the SQL?

Sorry I am not good at SQL and need your expertise. If you need to see the table structures, it is attached. Thanks.

[NeoPa]

It's generally possible to add fields to a table after it has data in it. You will need to ensure that any rules or understandings you have about how the data should be are maintained. This may not happen automatically for you.

Whether this (the new query the form is bound to) is still possible to update within the form depends on various factors. Check them out at Reasons for a Query to be Non-Updatable. Ultimately, you will know if it works when you try it and it does ;)

Not sure about Q2. You would obviously add the new tables in and link them. Not sure what else you would need to know. It shouldn't be complicated.

[MNNovice]

NeoPa:

The SQL above refers to the current query with tblGrantFunds, tblFunds and tblGrants. Question no. 2 referred to the modification needed to include tblProjects to the query.

Additionally I will be adding a field to tblGrantFunds called FedRatio (delete those fields with the same name from other tables) or SplitRatio. Then I can add a calculated textbox to frmAP to find the NetAmount based on the split ratio. Something like NetAmount = APAmount * FedRatio. At least, my line of thoughts were generally in that direction. I wouldn't know if it would work or not until I try.

I can accept my failure for not understanding things that are "not complicated" but I cannot accept not trying. That's how I am looking at it.

Thanks.

[NeoPa]

@MNNovice
This certainly sounds like progress.
@MNNovice
Sounds like determination. Always good to have.

I'm just a little confused as I would expect you to be working within the QBE (Query By Example) grid, that Access provides for designing queries graphically. This doesn't make the whole process suddenly very simple, but I would have thought you'd done a fair bit of this sort of thing before. Relatively straightforward.

Certainly, you can design all your SQL directly if you prefer, without using the graphical assistance, but there's no real need for this unless or until you feel comfortable doing it this way. Doing things remotely for others often requires this as the objects they use are generally not available to you anyway, but this wouldn't be an issue for you I expect.

[MNNovice]

NeoPa:

First of all I have not been very good at working with Query. But whenever I did, I sued QBE.

A few weeks ago Denburt showed me how to directly use the SQL for this one query. I don't quite understand all the terminologies for this to work. As such I posted the questions.

To my pleasant surprise I was able to worked out the Net Amount calculation. What I did is to add two fields to tblGrantFunds: ProjectID (to tie in the Project Number) and FedRatio. So far the reports are working.

I should close some of my postings. Is it okay to just simply delete them?

Thanks for your help and suggestions. Thanks to ChipR, Denburt and FishVal for their help as well.

Regards. M

[NeoPa]

Very pleased to hear that's working for you M.

As for closing / deleting your threads, this is not possible really. From your point-of-view however, a simple thank you post explaining you're happy with the answers will make it clear you no longer need assistance on the point.

We don't delete old threads as :

1. It just creates more work for us.
2. Old threads are part of the value of the site. New people are frequently searching for solutions through them. We don't delete old threads.