473,289 Members | 1,756 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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

418 256MB
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

Expand|Select|Wrap|Line Numbers
  1. SELECT tblGrantFunds.GrantFundID, tblFunds.FundNo, tblGrants.GrantNo, tblFunds.FundID, tblGrants.GrantID
  2. FROM tblGrants RIGHT JOIN (tblFunds RIGHT JOIN tblGrantFunds ON tblFunds.FundID = tblGrantFunds.FundID) ON tblGrants.GrantID = tblGrantFunds.GrandID
  3. 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.
Attached Files
File Type: zip Tables.zip (2.6 KB, 64 views)
Jun 16 '09 #1
5 1305
NeoPa
32,554 Expert Mod 16PB
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.
Jun 17 '09 #2
MNNovice
418 256MB
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.
Jun 17 '09 #3
NeoPa
32,554 Expert Mod 16PB
@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.
Jun 18 '09 #4
MNNovice
418 256MB
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
Jun 18 '09 #5
NeoPa
32,554 Expert Mod 16PB
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.
Jun 18 '09 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Eric | last post by:
I'm trying to insert data for a real-time application that demands less than 3 second receipt-to-commit time, has a relatively large (500,000 records/hr) data rate, and requires access times on the...
7
by: Warren Wright | last post by:
Hello, We maintain a 175 million record database table for our customer. This is an extract of some data collected for them by a third party vendor, who sends us regular updates to that data...
2
by: HumanJHawkins | last post by:
Hi, I am using data from multiple databases and/or queries. It would greatly simplify and speed things up if I could use CONTAINS in processing the results. However, "CONTAINS" requires the data...
2
by: G.W. Lucas | last post by:
I apologize if this is a RTFM question, but I have not been able to find a definitive answer elsewhere. Does a "REINDEX TABLE" lock the table while it is working? Can applications write data to...
4
by: dixie | last post by:
I have a table called "tblParticipants" with a field called "ID", which must be indexed with No duplicates allowed. I am looking for a way of doing in vba a small if ..then ..else .. routine that...
5
by: Sami | last post by:
Please bear with me, and if you answer this question, please do it step by step. I am new at Access, not at all sophisticated. I am using Office XP. This will need to be read in Access for...
3
by: lauren quantrell | last post by:
In a table design, properties window there is the Indexes/Keys tab. I want to create a few indexes. Row myID is the PK. I also want indexes on rows myOne, myTwo, myThree. In the selected...
2
by: Ravi | last post by:
Hi, I am working on a winform app. I need to use an object which can store some information(key/value pairs) and also can be acessed by multiple threads(read/write). From what I heard Hash table...
10
by: Steve Atkins | last post by:
I have a large table (potentially tens or hundreds of millions of rows) and I need to extract some number of these rows, defined by an integer primary key. So, the obvious answer is select *...
1
by: B | last post by:
Hello All, This is my first time using this list, but hopefully I got the right one for the question I need to ask :). I have a table which has about 4 million records. When I do a search...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.