Add a table

[Glennzo]

Hello again all. Is there any particular reason why, when I add a new table to an existing query, and add fields to the query from said table, I cannot edit the new or previously existing fields when I run the query?

Thanks for your time and consideration.

Glennzo

 

[Pat Hartman]

You did not create a join between the two tables. Open the query in design view and "draw" a line to connect the linking fields in the two tables.

 

[llkhoutx]

You may have created a non-updatable recordset. The status bar will indicate same.

 

[llkhoutx]

You may have created a non-updatable recordset.

 

[Glennzo]

I did create a join, one to many, between the 2 tables. How about that non-updatable table? When one creates a table, is there a parameter that makes in updatable or non-updatable?


Glennzo...

 

[llkhoutx]

No, non-updatable recordsets are the result of joins which Jet can't figure out which field belongs to which table.

 

[Pat Hartman]

Try posting the SQL statement.

 

[Glennzo]

Here's the SQL. Hope this isn't a repeat post. Was using Netscape and it didn't seem to work...

SELECT tblDirectory.MemberId, tblDirectory.LastName, tblDirectory.FirstName, tblDirectory.Address, tblDirectory.Address2, tblDirectory.City, tblDirectory.State, tblDirectory.ZipCode, tblDirectory.HomePhone, tblDirectory.Pledge, tblDirectory.EnvelopeNumber, tblPledgeTypes.PledgeType, tblPledgeTypes.PledgeAmount, tblFirstQuarter.PDate, tblFirstQuarter.PAmount
FROM (tblDirectory LEFT JOIN tblPledgeTypes ON tblDirectory.MemberId = tblPledgeTypes.MemberId) LEFT JOIN tblFirstQuarter ON tblPledgeTypes.PledgeId = tblFirstQuarter.PledgeId
WHERE (((tblDirectory.Pledge)<>0))
ORDER BY tblDirectory.EnvelopeNumber;


Thanks for your time folks!
Glennzo

 

[llkhoutx]

I checked your query, it is indeed non-updateable. This is just one of the mysteries of Access. program around it.

You don't say how you use this query, maybe it's the recordsource for a form. If so, I'd suggest making each table a recordsource for a form or subform on the form.

I did a church contribution program years ago on a TRS-80 in VB and recorgnize you tables.

Do you have to use your query? What do you use it for?

 

[Pat Hartman]

Try including PledgeID. If that doesn't work, try including the keys from both sides of the joins.

But, the suggestion to use nested subforms will solve the problem since each form will draw data from a single table.

 

[Glennzo]

I'm using the query to filter out the 70 pledging members from a table of about 200 members. That way, only the pledging members are displayed on the form. Given my relative lack of Access experience, I assume that this is the way to do it.

I have a working model of this church database that I started writing last year. It's used on a weekly basis and functions well for our needs. Data entry is easy, all calcultions are accurate, and there are several relevant reports. We even used it in January of this year to produce year 2002 member statements that were given out at church. Only one person told us that there was an error in their statement. All in all a fairly successful endeavor. There are really 2 or 3 issues now that are stalling my efforts.

One concerns an option group. I want to trap any change to the currently selected option and ask the user, with a message box, 'are you sure you want to change this option'. If the answer is yes, the change is made. If the answer is no the change is cancelled. How is this done? The reason I want to do this is because any change in the 'pledgetype', which is what the option group determines, changes several critical calculations.

Next, I'd like to attach a real date to all data entered. The date is not necessarily 'today', but more likely the date of the Sunday that the donations were received. So the field that I called 'Jan1', which is for donations for the first week of January, would be linked to 01/05/03 and field called 'Jan2' (second week in January)would be linked to 01/12/03 and so forth. That way I could create reports based on a particular donation week, or for the month of August, etc. I have no real date field in the table. Could I add one at this point and make it functional in this way?

Thanks for any consideration. Hope I'm not to long winded or vague here...

Glennzo...