OK I feel better already, sharing my confusion with someone. Sorry about
that.
The CorpName (as it is actually called) does appear in the Inquiries
table,
yes, but the entries are all numerics, being the IDs from the respective
records in the Corporates table.
This despite the fact that the Inquiries form manages to correctly show
the
actual CorpName in its combo box - but maybe that's irrelevant as we're
talking tables and queries here...
I have tried changing the Bound Column in the Inquiries table but it made
no
difference, it still insists on pulling the ID.
I guess it is more of a table contents issue, rather than just my query,
which is suffering merely as a result of something wrong in the table
relationship or the lookup?
You're welcome to the SQL which is as follows...as you can see there are
just a few other fields involved in this query, which I use as the source
of
multi-table mergefields for my documents - that's why there are so many
fields in there.
SELECT Inquiries.Ref, Inquiries.Customer1Title,
Inquiries.Customer1FirstName, Inquiries.Customer1Surname,
Inquiries.OriginAddr01, Inquiries.OriginAddr02, Inquiries.OriginAddr03,
Inquiries.OriginCounty, Inquiries.OriginPostCode, Inquiries.OriginCountry,
Inquiries.DestAddr01, Inquiries.DestAddr02, Inquiries.DestAddr03,
Inquiries.DestCounty, Inquiries.DestPostCode, Inquiries.DestCountry,
Corporates.CorpName, Inquiries.InquiryDate, Inquiries.InquirySource,
Quotes.SpecialAttention, Quotes.Mode, Quotes.TotalQtdCharge,
Quotes.TotalQtdChargeCopy, Quotes.StorageQtdCharge, Quotes.HandlingCharge,
Quotes.InsuranceType, Quotes.InsuranceQtdPcent,
InsuranceRates.InsAdminFee,
InsuranceRates.InsCharge, Quotes.AddSvc1Type, Quotes.AddSvc1Charge,
Quotes.AddSvc2Type, Quotes.AddSvc2Charge, Quotes.AddSvc3Type,
Quotes.AddSvc3Charge, Inquiries.SurveyDate, Inquiries.SurveyTime,
Inquiries.Surveyor, Inquiries.InquiryTakenBy, Quotes.TransitTime,
Inquiries.RemovalDate, Quotes.TransitTime, Inquiries.RemovalMonth,
Events.PackStartDateEst, Events.DelStartDateEst, Quotes.OriginAccess,
Quotes.DestAccess, Inquiries.OriginHomePhone, Inquiries.OriginOfficePhone,
Inquiries.OriginMobile, Inquiries.OriginFax, Inquiries.OriginEmail,
Inquiries.DestHomePhone, Inquiries.DestOfficePhone, Inquiries.DestMobile,
Inquiries.DestFax, Materials_Crates.TotalCubeM3, Events.LoadStartDateEst,
Events.UnpackDateEst, Volumes_Weights.VolNetM3Qtd,
Volumes_Weights.VolNetCuFtQtd, Volumes_Weights.WtNetKgsQtd,
Volumes_Weights.WtNetLbsQtd, Materials_Crates.Pk2ChinaQty,
Materials_Crates.Pk2Qty, Materials_Crates.Pk3Qty, Materials_Crates.Pk6Qty,
Materials_Crates.RobeQty, Materials_Crates.[P-CQty],
Materials_Crates.PaperQty, Materials_Crates.BubbleQty,
Materials_Crates.BikeBoxQty, Materials_Crates.FurnCubeM3,
Materials_Crates.FurnCubeCuft
FROM (((Inquiries LEFT JOIN (Events RIGHT JOIN (Quotes LEFT JOIN
InsuranceRates ON Quotes.InsuranceType = InsuranceRates.InsType) ON
Events.Ref = Quotes.Ref) ON Inquiries.Ref = Quotes.Ref) LEFT JOIN
Materials_Crates ON Inquiries.Ref = Materials_Crates.Ref) LEFT JOIN
Volumes_Weights ON Inquiries.Ref = Volumes_Weights.Ref) LEFT JOIN
Corporates
ON Inquiries.CorpName = Corporates.CorpName;
Will be great if you can come up with something - many thanks Brendan.
CW
Brendan Reynolds said:
I'm a bit confused at this stage as to just which fields in which tables
we're looking at. Perhaps you could you post the SQL? (I'm assuming that
the
column isn't actually blank in the table?)
--
Brendan Reynolds
Access MVP
Aaaargh! Of course...I wasn't querying the correct table, where the
companyname actually lives.
However, having now brought in that table and field, with a one-to-many
join
("2") on companyname from Inquiries to Customers, it now doesn't give
me
anything AT ALL in the name column. Complete set of blanks!
What now, please?
Many thanks
CW
:
It's probably a lookup field. Open the table in design view, select
the
companyname field, and click the 'Lookup' tab in the Field Properties
pane
at the bottom of the table design view. Do you see a 'Row Source'
property?
If so, note the name of the table it is selecting from. You'll need to
join
that table into your query to include the real company name field.
--
Brendan Reynolds
Access MVP
I have a Customers table that includes "companyname" and various
other
details.
I have an Inquiries table and a form based upon it.
On that form I have a combo that looks up the company names and
pulls
them
through correctly, no problem.
I have now built a Query that includes Customers/companyname, but
instead
of
pulling through the name it gives me the ID numbers of the
companies,
not
their names.
I assume it is simply looking at the wrong column in the table.
I know how to manipulate bound columns in Forms, i.e. through the
properties
of the relevant control, but how do you tweak this in a Query?
(I have looked at the SQL view of the Query and it says it is
selecting
companyname. But it is not!!)
