SOLVED

Access and Excel wizard query blank beside headers

Copper Contributor

I imported an excel datasheet (Lastname, firstname, Healthins, Retirement, TotalBenefits) into Access and created a relationship with an Access database (Lastname, Firstname, Hiredate, job title, salary) based on last name. When doing a simple wizard query (Access: Lastname, Firstname, Hiredate, Jobtitle / Excel: Totalbenefits) the query returns the correct headers but otherwise remains blank. In the design view it shows the correct tables and view as checked. 

 

I'm a new user to Access (this is a class lab assignment). I followed the instructional video to a T, but when the professor runs the query the results populated without a hitch. 

11 Replies
In order to help us help you, please provide more specifics in addition to the general description.

We need to see the SQL in the attempted query, preferably from the SQL view.

Also, data. Show us the data. Screen shots of the two tables should work.
How did you Import your datasheet?
If you use Copy/Paste, chances are there are "hidden" (non pritable) characters (mostly at the end) of your imported data.

@George Hepworth 

I'm happy to try and provide anything, not sure what the SQL is- If you can give me an idea of what to look for I'll do my best to find it. (Prior to this class, I've never used access- so thank you for your patience).

The names, addresses, etc are all made up for the assignment. 

AccessExcelTable.pngAccessTable.png

I imported using the External Data Tab> New Data Source > From File > Excel
Maybe my eyes are failing, but it sure looks to me like the First and Last Names are swapped in those tables in your screenshot

I did find "SQL View" not sure if this is what you are looking for or not! 

 

I did email my instructor about the assignment, and she said "Looks like you have the table and query linked properly but no formula. Assuming your table has records, that would show all. You need a formula to finish the lab"

But didn't clarify further (no mention of a formula needed in her written instructions/videos) and referred me to visit her during office hours... Lab is due Sunday, her next office hours aren't until Wednesday. I emailed her about that but she's notoriously bad about getting back to students before 3-4 days have passed. If I absolutely have to, I can just submit what I have for partial credit but I absolutely hate not knowing/learning how to complete something. 

 

I absolutely appreciate any/all help, even if I don't manage to figure it out in the end. SQLview.png

Oh, good point. Would that make a difference? I can go and reverse it.

Reversing First/Last name order didn't change anything for me- so I'm not sure where I'm going wrong!

ReversedFirstLast.png

 

best response confirmed by GennyK (Copper Contributor)
Solution
Look again? The VALUES for FirstName in one of them are in the LastName field in the other, and vice versa.

OHHHHHHH!!!!!
It's because she had us copy and paste the names from access where she had us add first names first into excel, where she had us use last names first. Omg. This entire semester we've not been given us a single assignment where following the instructions to an exact T has worked successfully.
Ever considered teaching classes? Please replace my professor, I'm been agonizing over "what missing formula?" for nearly a week.
The new query did work, thank you so sooooo much.

Thank you for the kind words. I've been retired for several years. Posting in forums like this is as close to teaching as I get these days.

Congratulations on solving the mystery. Continued success with the class.
1 best response

Accepted Solutions
best response confirmed by GennyK (Copper Contributor)
Solution
Look again? The VALUES for FirstName in one of them are in the LastName field in the other, and vice versa.

View solution in original post