Updating Tables

[Mahira]

Good morning, I'm a newbie....and I inherited this db.

I have an existing table. I need to add two rows of information. When I add the rows, they are empty. I have tried running a query with all the fields from the table plus the two rows I need, but I loose 90+ records. What am I doing wrong???

Thank you for any help...

 

[CraigDolphin]

If all you want to do is add 2 more rows(records) to a table then just open the table and input the values at the end of the table.

If you want to do this via a form and are having trouble then we either need a lot more information, or a sample database to look at to see what's wrong.

As for the query issue...what kind of query is it? Can you provide the SQL underlaying it? If the two rows are not already in the table, how are you getting all the rows from the table plus the 2 rows that aren't in the table?

 

[Mahira]

I apologize for not being clearer. I need to add two columns. I have over 300 records in the table and need to update the two new columns.

It is a select query. I added all of the fields from the table plus the two fields from an existing query.

How do I find the underlying SQL?

 

[rainman89]

open the query in design view and then click view > SQL view

 

[Mahira]

Thank you...here is the SQL

SELECT [ALL 05/06Fall].ID, [ALL 05/06Fall].SSN, [ALL 05/06Fall].[Last Name], [ALL 05/06Fall].[First Name], [ALL 05/06Fall].[Middle Name], [ALL 05/06Fall].[Maiden Name], [ALL 05/06Fall].Address, [ALL 05/06Fall].City, [ALL 05/06Fall].State, [ALL 05/06Fall].[Zip code], [ALL 05/06Fall].[E-Mail], [ALL 05/06Fall].PhoneNum, [ALL 05/06Fall].[Date of Birth], [ALL 05/06Fall].Race, [ALL 05/06Fall].Gender, [ALL 05/06Fall].Transcript_ST, [ALL 05/06Fall].Transcript_Instit, [ALL 05/06Fall].[Grade Point], [ALL 05/06Fall].FirstYear, [ALL 05/06Fall].FeePaid1, [ALL 05/06Fall].ReceiptNum_1, [ALL 05/06Fall].DateFeePaid_1, [ALL 05/06Fall].CBC_AR, [ALL 05/06Fall].CBC_FBI, [ALL 05/06Fall].P1_ReadTestDate, [ALL 05/06Fall].P1_WriteTestDate, [ALL 05/06Fall].P1_MathTestDate, [ALL 05/06Fall].P1_ReadScore, [ALL 05/06Fall].P1_WriteScore, [ALL 05/06Fall].P1_MathScore, [ALL 05/06Fall].Yr1_GRE, [ALL 05/06Fall].Yr1_MAT, [ALL 05/06Fall].Yr1_LSAT, [ALL 05/06Fall].Yr1_MCAT, [ALL 05/06Fall].Other, [ALL 05/06Fall].NeedReading, [ALL 05/06Fall].CompletedReading, [ALL 05/06Fall].NeedAR_History, [ALL 05/06Fall].CompletedAR_History, [ALL 05/06Fall].[Degree 2], [ALL 05/06Fall].ResumeReceived, [ALL 05/06Fall].LicTeachArea, [ALL 05/06Fall].[Hire Date], [ALL 05/06Fall].[Hire Date_2], [ALL 05/06Fall].[Def Comments], [ALL 05/06Fall].[Alternate Phone], [ALL 05/06Fall].[CompletedReading 2], [ALL 05/06Fall].Subject1, [ALL 05/06Fall].Subject2, [ALL 05/06Fall].Comments, [ALL 05/06Fall].[Graduation Date], [ALL 05/06Fall].[Degree 3], qryNonTradLicAreas2.[Area Code], qryNonTradLicAreas2.[Area Abbreviated Desc]
FROM [ALL 05/06Fall] INNER JOIN qryNonTradLicAreas2 ON [ALL 05/06Fall].ID = qryNonTradLicAreas2.ID;

 

[Mahira]

I have finally managed to figure out what is happening (I think). You click on a menu item that runs a macro. The macro opens and runs a make table query closes it then runs an append query closes that, and then opens the table (from the make table query) where I need to add the columns.

The queries are attached to two different tables, but neither has the information I need to add. That info is in a different query.

 

[CraigDolphin]

You will need to modify your make-table query so that it creates the two new columns (fields) that you want to appear. Then you need to modify your append query so that it populates the two new fields in addition to the original fields that are already there.

You can draw information from more than one table/query into an append query.

If you need help with that it would be easier if you post a stripped down (no sensitive data), zipped copy of your db.

 

[Mahira]

Thank you ... I got everything updated. The append query works perfectly. The make table query think I'm trying to delete it....

 

[CraigDolphin]

Make-table queries delete the table prior to making it, so if your macro runs the make table query and you haven't added the two fields to the make table query, then the two new fields will 'dissappear' every time it is run by the macro. It's easy enough to add two placeholder fields to the make table query. Just add two new column headings in design view. Something like...

YourColumnName1Here: ""
YourColumnName2Here: ""

...in the field row of the query in design view.

 

[Mahira]

I have updated the make table query with the fields I want to add.

I get an Action Failed message with the macro name ... condition: True ... Action Name: Open Query ... & Arguments: queryname, Datasheet, Edit. When I click 'Halt' I get 'You can't delete the table 'table name'; it is participating in one or more relationships.

 

[CraigDolphin]

Have you added the table to the relationships view of the db and specified relationships to other tables? If so, then this will prevent the make table query from deleting it. Remove it from the relationships window after removing any relationships you may have added.

If this only occurs after adding the two additional fields to the make table query then I'm not sure what's going on. It's hard to comment without seeing the database.

 

[Mahira]

It worked!!!! Thank you sooooo much..

Have a great weekend!!!

 

[CraigDolphin]

Glad it worked out