Using MS Access/Access 2007 Query

Advertisement


Question
QUESTION: I have copied a database from 2013 to use in 2014.  One of the queries which was editable in the 2013 version will not allow editing in the 2014 and I have not changed any settings.

I know editing in a query is not recommended but there is a need in this case.  Why would I be able to edit a query in one database and not the other?  I have had them up on my screen side by side one works and the other does not.

ANSWER: What do you mean by copied from 2013 to 2014? Do you mean you have copied a database used for 2013 data to use with 2014 data?

This is not really necessary. You should just have a date field to identify when the records are from.

Or are you using different versions of Access? If so which versions, there is no 2014 version. Its 2007, 2010 and now 2013.

What happens when you try to edit data? What is the SQL for the query? And why do you think you need to edit in a query?

Hope this helps,
Scott<>


---------- FOLLOW-UP ----------

QUESTION: Copied a 2013 database then deleted the data so that it can now be used for the 2014 function.

Access 2007

When I click on a cell I get a sound/ding and am not able to type anything new or edit what is there.

When I audit data entry in a query and see that they may have made an error the query is a good place to make a quick fix.

I just made quick query in both databases using exactly the same tables and fields and have the same problem, here are the SQLs for both.

2013 (this one works)
SELECT [CONTACT DEMOGRAPHICS].ID, ReservationsTable.TableNumber
FROM [CONTACT DEMOGRAPHICS] INNER JOIN ReservationsTable ON [CONTACT DEMOGRAPHICS].ID = ReservationsTable.[Contact ID];

2014 (this one does not work)
SELECT [CONTACT DEMOGRAPHICS].ID, ReservationsTable.TableNumber
FROM [CONTACT DEMOGRAPHICS] INNER JOIN ReservationsTable ON [CONTACT DEMOGRAPHICS].ID = ReservationsTable.[Contact ID];

ANSWER: So you are trying to change a table number?

Try this query:

SELECT [Contact ID], TableNumber
FROM ReservationsTable
WHERE [Contact ID] IN(SELECT ID FROM [Contact Demographics]);

But the best way to do this is to use a Mainform bound to Contact Demographics and a Subform bound to ReservationsTable and linked on ID to Contact ID. You search for a contact and then change the table numbers.

And, as I said, there is no reason to maintain separate databases for a year.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA


---------- FOLLOW-UP ----------

QUESTION: Yes trying to change the table number, and what you sent me worked in the simple example query I provided to you, but I'm not sure how to fix the actual real query...here is the SQL for that one which works in the 2013 database but cannot edit in the 2014 database.

SELECT ReservationsTable.VIP, ReservationsTable.[Reservations Adjusted Tkt No], ReservationsTable.[Special Ticket Guests], ReservationsTable.[Special Ticket], ReservationsTable.Comments, ReservationsTable.ReservationsID, ReservationsTable.[Sits With], ReservationsTable.TableNumber, ReservationsTable.[Seat Number], Format([TableNumber],"00") & "" & Format([Seat Number],"00") AS BidderNumber, IIf([Reservations Payment Method]="paid by table host","yes") AS Guest, ReservationsTable.[Reservations Payment Method], ReservationsTable.[Reservations Number of Tickets], ReservationsTable.[Reservations Ticket Price], [Reservations Number of Tickets]*[Reservations Ticket Price] AS [Reservations Amount Total], ReservationsTable.[Reservations Amount Paid], [Reservations Amount Total]-[Reservations Amount Paid] AS [Reservations Balance Due], ReservationsTable.[Name of Event], ReservationsTable.Sponsorship, ReservationsTable.Donation, [Contact Demographics].ID, [Contact Demographics].SALUTATION, [Contact Demographics].LastName, [Contact Demographics].FirstName, [Contact Demographics].MI, [Contact Demographics].Suffix, [Contact Demographics].SPOUSE, [Contact Demographics].[Company/Foundation], ReservationsTable.[Contact ID], ReservationsTable.[Reservations Date Entered], ReservationsTable.[Table Host], [Contact Demographics].[Telephone--HOME], [Contact Demographics].[Cell Phone], [Contact Demographics].[Business Phone], IIf(IsNull([Telephone--HOME])=False,[Telephone--HOME],IIf(IsNull([Cell Phone])=False,[Cell Phone],[Business Phone])) AS ContactPhone, IIf(IsNull([Address]),[Company Address],[Address]) AS [Mailing Address], IIf(IsNull([City]),[Company City],[City]) AS [Mailing City], IIf(IsNull([State]),[Company State],[State]) AS [Mailing State], IIf(IsNull([Zip]),[Company Zip Code],[Zip]) AS [Mailing Zip]
FROM ReservationsTable INNER JOIN [Contact Demographics] ON ReservationsTable.[Contact ID] = [Contact Demographics].[ID]
ORDER BY ReservationsTable.ReservationsID, ReservationsTable.[Sits With];

Answer
Whenever you use a multi-table query, you run the risk of it being uneditable. I suspect the reason it may work in one and not in the other is because of the data.

The best answer I can give you is to not use a query but use a mainform/subform as I suggested.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.