Pulling data based off a list

[Kookymonster]

Hello guys,


I'm new to this forum and I was hoping you guys could guide me!
I'm a fairly competent user of excel, but it just was not cutting it for managing a database. I'm trying to learn how to use Access, and I came across something that feels like it should be easy to do but hours of youtubing and googling have not gotten me anywhere.

So, I have a one table that has product ID, description, and location. This spans tens of thousands rows.

Now I have a second table that has just a list of maybe a thousand product ID's I want to check. Is there a way to retrieve the description and location from the first table for the product ID's in the second table?

Once again I'm a big novice to Access and would appreciate any guidance!

[Bob Fitz]

Hi Kookymonster

Welcome to the forum

This would be done with a query. Take a look at this link

http://office.microsoft.com/en-us/ac...010096275.aspx

Please post back if you need more help.

[Kookymonster]

Thank you for your reply, Bob Fitz.

I had a feeling queries were the answer and played around with them.
I can get them to work on the Northwind sample database, but not with mine.

Maybe I'm doing the relationships wrong? I noticed on the Northwind database, it has the 1 and infinity sign on the arrows. The relationships lines on my database don't have them. When I run the query on my database, I only get a blank spreadsheet as my result. Any ideas?

Thank you!

[Bob Fitz]

You will need to decide which fields you want to see in the results. If you double click a field, Access will add that field to the lower section of the design grid.

[Kookymonster]

I'm assuming you are talking about on the query wizard, correct?

I know this might seem pretty trivial to you, but for the life of me, I can't figure it out.
Do you think you can look at the attachment?

The test list is a list of 10 ID's that I want to get information for.
The TestDatabase has 200 ID's that have additional info attached to them.

When I try to get the query, It shows the two fields I want, but there is no information in those fields.

I've uploaded it to mediafire because I couldn't get the accdb file to be under 500kb. If that's against forum rules, I'll apologize and take it down.

http://www.mediafire.com/?553vbd46qihi6ss

Thanks

[Bob Fitz]

I'd be happy to look at db, but it needs to be saved and posted as an A2003 mdb file for me to be able to do so.

[Kookymonster]

My apologies. I've converted it.
Thank you so much!

[Bob Fitz]

OK. I've had a look at your db. The reason the query does not return any records is that there are no matching records in the two tables . If you enter some matching records it works.

[Kookymonster]

Hey Bob,

I apologize but I'm afraid im terribly confused now. =[
By matching records, you mean the IPI followed by 8 digits, right?
I just checked the 10 ID's in the Testlist and made sure they were in the bigger list.
Forgive me if I'm overlooking something simple, but I'm darn sure each ID on Testlist is in Testdatabase.

Edit: Oh my goodness, I just realized each ID in the TestDatabase has a space before the ID.
Is there an easy way to erase the space? In excel I would write a macro, but I'm unfamiliar with how VBA works in access.

Edit: Did a simple find and replace to get rid of the space. Wow. Can't believe a space took 2 days out of my time. -_-. Here I was, thinking MS Access must be the most cryptic program I have ever used.

Thank you so much bob for helping me! If it wasn't for you, I would have probably flipped my table over in frustration. =]
（╯°□°）╯︵ ┻━┻

[Bob Fitz]

Yes that is easily done with an update query.
In the attached db I have done that for you.
Your query now returns some records .