How do I pull address from table onto my report

**tanyapeila** · 03-19-2014, 03:22 PM

Ugh, it's been one of those days.

See my screenshot.
I have an "Inbound" form that you enter all of the fields listed.
The "Cont C/O" field is the same as the "Agent" field in the LOCAL AGENTS Table.
When I pull my report it pulls ALL of the Agents in the table not just the one selected for this record.

HELP

Click image for larger version.

Name: Capture.PNG
Views: 21
Size: 37.3 KB
ID: 15819

Click image for larger version.

Name: Capture.PNG
Views: 21
Size: 21.9 KB
ID: 15820

Click image for larger version.

Name: Capture.PNG
Views: 21
Size: 70.4 KB
ID: 15822

**pbaldy** · 03-19-2014, 03:26 PM

You need a join between the appropriate fields in the 2 tables.

**tanyapeila** · 03-19-2014, 03:51 PM

I tried to join the "Cont C/O" and "Agent" fields but then NO records pull

**pbaldy** · 03-19-2014, 04:20 PM

That would seem to contradict "The "Cont C/O" field is the same as the "Agent" field in the LOCAL AGENTS Table." Can you post the db here?

**tanyapeila** · 03-19-2014, 04:22 PM

Originally Posted by pbaldy

That would seem to contradict "The "Cont C/O" field is the same as the "Agent" field in the LOCAL AGENTS Table." Can you post the db here?

How do I post the db?

**tanyapeila** · 03-19-2014, 04:30 PM

I think it may be too big. It will not let me attach it.

**pbaldy** · 03-19-2014, 04:54 PM

If you do a compact/repair then zip, it may work.

**tanyapeila** · 03-20-2014, 09:29 AM

Let's try this!

**tanyapeila** · 03-25-2014, 11:50 AM

Paul, were you ever able to open my database?

**pbaldy** · 03-26-2014, 09:35 AM

I was but I got an error on that field, even just trying to sort it. It now occurs to me that I opened it with 2007, so perhaps I'll fire up a VM with 2013 on it and try, since that's what you have.

**tanyapeila** · 03-26-2014, 09:38 AM

Awesome thank you for all your help! Drives me nuts to sort through 100+ pages to find the one I need!

**pbaldy** · 03-26-2014, 11:08 AM

I think I see the problem. You've used a lookup field and have incompatible data types. The "Cont C/O" field in the inbound table uses a lookup field, which I don't care for. In any case, the data type of the field it looks up to is Autonumber (the ID field in Local Agents), the field in Inbound is Short Text. It should be Long Integer to be compatible. More on lookup fields:

http://access.mvps.org/access/lookupfields.htm

Also, you'll find that having spaces and symbols in your field names isn't worth it in the long run.

**pbaldy** · 03-26-2014, 11:30 AM

Oh, and in the relationships window, your join is to the wrong field in Local Agents. It should be to the ID field, not the Agent field. The lookup field masks what is really being stored in the Inbound table, which is actually the ID field.

**tanyapeila** · 03-26-2014, 12:00 PM

I changed the "Cont C/O" field to be a Long Integer. I also updated the relationship to "Cont C/O"(Inbound) to "ID"(Local Agents)
Now when I run the report I am getting this error