Help in changing an existing database

[adamaphar]

I would love some help in understanding how to modify an existing database. I am very unfamiliar with using Access. I use it now and then to work with data, but I am basically just following rote written instructions on how to complete the task without any clue what those instructions are doing.



But, the problem is, I need to add a field to the database to reflect an added column of data in the excel spreadsheet that I am working from.

So, after playing around for a while, here is my idea of what is going on. Data is importing into a table in Access. Then, I use a form to generate a report. When I click on a button in a form, then it initiates a macro... or a query... or possibly both, which then puts the data into the report. This is the "I'm an idiot" version. If I could understand what Access is doing with my data and how, then perhaps I could figure out how to add my new field.

I'm basically trying to get a basic understanding of the inner workings of Access. Any help would be great!

Thanks!

[Robeen]

One quick and easy way of making sure that you import your data from the Excel spreadsheet correctly is to use the External Data functionality built into Access and import your Execl spreadsheet [with the new row of data] into a new table in Access.

Now, look at the new table in design view [right-click on the table in design view and select Design] and see the name and data type of the new field that Access imported from Excel.

Open the existing table [that does NOT yet have the new field from Excel] and add the new field exactly as it is in the new table that was created when you did the import.

This will give your existing table and the spreadsheet the same fields.

What are the instructions you are following by rote?
If you can post them here - we can try and explain what exactly is happening.

[adamaphar]

Here are the directions. As you can see, I am using the import external data wizard.

1. Click on External Data>Text File. Click Browse and selectthe file that contains the data for the course or student that you want to run the report on and click Open. (Make sure the Import the source data into a new table in the current database option is selected. Click OK.)
a. A new window pops up; select Delimited for the format and click Next.
b. In this new window:
i. Mark the box next to Comma in the “Choose your delimiter that separates your fields:” heading
ii. Mark the box next to First Row Contains Field Names. (A pop-up may open stating “The first row contains some data that can’t be used for valid Access field names. In these cases, the wizard will automatically assign valid field names,” select OK to this message.
iii. Under Text Qualifier, select the quotation marks (“).
iv. Select the Advanced button and select the Specs button, then highlight “TIPR Import” and select Open, then OK.
v. Select Next, don’t change anything on the next page, select Next again and make sure that “Let Access add primary key” is selected.
c. Select Next one last time, you are now on the final window you are at the final window (the Next button will be grayed out).
i. Under Import to Table heading type “FTC” and select Finish.
ii. Answer Yes to Overwrite existing table or query ‘FTC’? and click on OK to “Finished importing file ‘File Name’ to FTC.
d. A new dialogue box will pop up.
i. *If this box informs you of import errors, there will be mistakes in the report. Look for the mistake(s) (e.g. missing ratings and/or comment mistakes), attempt to resolve and re-import into Access.
ii. This pop up dialogue box should tell you that the data has been successfully imported, and asks you if you want to save these import steps, do not save and simply “close” out of this box.
2. Go to the main database form. From here, there are buttons to generate reports for first or second half only, an individual candidate only, or an entire semester.


So, I can successfully get the data into Access. I've also been able to add the text box to the reports. It's telling Access to add the data to the reports that is the problem. What I have so far is, the button on the form tells Access to run a macro. This macro then tells Access to run a query and three reports. So when I go into design view in the query, I see that it is pulling data from all the fields in the table that I imported. But I can not figure out how to add my new field to the query. My hope is that if I can figure that out, I will be able to get to where I want to be.

[Robeen]

When you look at the query in design view - do you see the box in which all your Table fields are?
The new field needs to be in that box.
If you see the new field - then just double-click it & it will be added to the fields that your query displays when you run it.

If your new field is NOT in that list - then your query is probably pointing to the wrong table.

Hope this helps!

[adamaphar]

Thank you, it does help. I realized that the query was pointing to another query, which in turn was pointing to three other queries. Those queries, however, were pointing to my table - so I was able to get it to accept the new fields.

I am still stuck however, and not exactly sure what the problem is. I added the new field to the form, but when I click the button to generate the reports, I get an 'Action Failed' window. It looks like a problem with the macro. Usually it says "property not found," though once it said something like "action not recordable."

So I've got my form. When I look in design view and select one of the commands, it says On Click - Event Procedure. So when I look at the three little dots next to event procedure it opens the Microsoft Visual Basic. I take it I am looking at the code for the database. And this is what I'm looking at for the code associated with Command1.

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
Dim stDocName As String
stDocName = "TIPRFIRSTHALF"
DoCmd.RunMacro stDocName
Exit_Command1_Click:
Exit Sub
Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

I assume this means that when I press this button it calls up "TIPRFIRSTHALF" macro. So cool, I look at this macro, and it says:

Echo
SetWarnings
OpenQuery (these are the queries I changed)
OpenForm
OpenForm
OpenForm
Echo

The forms it wants to open are the ones that I added the text box to for the new field.

So that is where I am at. Thanks very much for your help so far.

[Robeen]

Do you still need help with this?
If so . . . can you post the database here?

[adamaphar]

Thank you, actually I was able to resolve it. Turns out the query was creating two other tables, and then using those tables to populate the report. But thank you very much for your help.