Lookups in table problems

[Slab_Rankle]

Hey guys,

I seem to have fallen victim to the evils of lookup fields...I'm trying to create a query that I can use in a mail merge, but my current table setup is giving me a lot of problems. Basically I have two tables, one called Creditors and one called CreditorInfo. Creditor info stores all the information about a creditor including their name, address, telephone numbers etc. The table Creditors stores different information, BUT, it looks up the contact details of a Creditor from the CreditorInfo table.

The second table (just named 'Creditors') looks up the name of the Creditor from CreditorInfo table and stores it in a field called 'Creditor'. There are two columns for this field, the first one stores the CreditorID from CreditorInfo and the second storing CreditorName. Now, when I do a mail merge it displays the CreditorID, NOT the CreditorName. Before I post my table structures I'm wondering if anyone would need to see every table (I can upload the backend) or just the two table structures that have been mentioned.

I realise this problems is occuring because I've used a lookup, which is incredibly evil, and I'm wondering if there's a way to fix this before I end up going insane....if anyone could help that'd be great!

 

[spikepl]

What prevents you from getting rid of the lookups in your table, and then making a query that shows the desired fields from each table?

 

[Slab_Rankle]

I've tried to do that, whenever I introduce the CreditorInfo table to my query it makes all the results dissappear...here's the SQL to my query with the CreditorInfo table in it (note that there's some more tables/queries in there to get all the fields I need in there):

SELECT Creditors.PKey, [Basic Details].[Account Number], [Basic Details].[Client Name], [Basic Details].[Address 1], [Basic Details].[Address 2], [Basic Details].[Address 3], [Basic Details].Postcode, TotalDI.SumOfDisI, ExpectedPay.ExpectedPay, Creditors.Creditor, Creditors.Address1, Creditors.Address2, Creditors.CurrentAccNo, Creditors.OriginalAccNo, [Basic Details].CredFirstExpect
FROM CreditorInfo INNER JOIN (((([Basic Details] INNER JOIN Accounts ON [Basic Details].[Account Number] = Accounts.[Account Number]) INNER JOIN Creditors ON [Basic Details].[Account Number] = Creditors.[Account Number]) INNER JOIN ExpectedPay ON (Creditors.PKey = ExpectedPay.PKey) AND ([Basic Details].[Account Number] = ExpectedPay.[Account Number])) INNER JOIN TotalDI ON [Basic Details].[Account Number] = TotalDI.[Account Number]) ON (CreditorInfo.CreditorID = Accounts.Creditor) AND (CreditorInfo.CreditorID = Creditors.CreditorID)
GROUP BY Creditors.PKey, [Basic Details].[Account Number], [Basic Details].[Client Name], [Basic Details].[Address 1], [Basic Details].[Address 2], [Basic Details].[Address 3], [Basic Details].Postcode, TotalDI.SumOfDisI, ExpectedPay.ExpectedPay, Creditors.Creditor, Creditors.Address1, Creditors.Address2, Creditors.CurrentAccNo, Creditors.OriginalAccNo, [Basic Details].CredFirstExpect;

If I'm honest I think it's in a bit of a mess. I created this while I was still learning and now that I know a bit more I'm finding it difficult to repair it all....

EDIT: Oh, & I realise there's spaces in some of the names, one of the other mistakes I made when I started!

 

[jdraw]

As spikepl said -- get rid of the table level lookup.

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

 

[Slab_Rankle]

Hi jdraw,

When I remove the table level lookup it just provides me with the ID of the Creditor and not the value of CreditorName. Is there a way around this?

 

[jdraw]

Show us the table structures and relative details/descriptions and let see if anyone has some alternatives.

 

[Slab_Rankle]

Rather than list all my tables I'll just upload the back end, some of the fields will be blank due to some information I needed to remove!