Qry with two fields of same name from different tables?

[d9pierce1]

Views: 8 Size: 309.3 KB">CompanyTester.zip

HI all,
I attached a copy of a db I that I took parts from my main db as so to make smaller...

db opens to frmCompany, there are two tabs on it with sub forms.
The issue is that my query that relates to the Locations Tab, (CompanyByLocationQry) ... I need to put CompanyID from tblLocation and not sure how?
I have CompanyID from tblCompanyByLocation which i need for the qry to work but i also need the CompanyID from the tblLocation so it puts the CompanyID in that
tbl as well.

Just not sure how to do it? Suggestions are great...
Thanks
Dave

[Gicu]

Hi Dave,

your design is slightly wrong, you should use the IsPrimary from tblLocations and drop it in tblCompanyByLocation and also drop CompanyID from tblLocations as you already have established the join between the two in tblCompanyByLocation.
In any case, see if this is what you wanted, just added the field and renamed it then added a new textbox with the same default value as txtCompanyID.

Cheers,
Vlad

[Bob Fitz]

The issue is that my query that relates to the Locations Tab, (CompanyByLocationQry) ... I need to put CompanyID from tblLocation and not sure how?

Why?

I have CompanyID from tblCompanyByLocation which i need for the qry to work but i also need the CompanyID from the tblLocation so it puts the CompanyID in that
tbl as well.

Perhaps I'm not understanding all this correctly but I don't understand why you need to change anything.
What is not working correctly?

[orange]

Dave,

Can you provide a 3-4 line overview description of the business this database is intended to support?
From my initial view the tables don't really tell a story.
For example why not have address with the Company?
If the company has "subsidiaries" in different locations, then perhaps another table(s) may be needed.
What is the significance of IsPrimary and IsActive? What exactly is IsPrimary (the company, the address...)???
Why do you need a table of CompanyByLocation? You can get that from a query when needed.

[d9pierce1]

Hi all and thanks for the info...I will try to answer all of you in this reply

Orange,
This is a company database to track companies and contacts along with a lot of other info. As stated this is just a snip it of the real db as it is way to big to upload.
Each company can have multi locations, each location can have multi addresses for that location.
I dont want the companyID in the tbladdress as i use this for other entities also like people (Not Company Contacts) but personal records which dont have a company,
The IsPrimary is the lets say "Preferred" Location, address, and so on. It can only be one. The IsActive is if not active, then wont show up after my active (have not set yet in quy's)
but intend to once completed.
As far as the question of "Why do you need a table of CompanyByLocation? " my only answer is its the only way i know how to do it.

Bob,
The reason for the question is I didnt know the best way to get the CompanyID from tblLocation into the query without changing the name of the other CompanyID from the Junction table.

And finally Gicu,
That is pretty much what i was wanting. I will have to play with it. I though i may possible have to change the CompanyID in the Junction Table to CompanyIDfk or somehting and then put a new
TxtBox on the form and just make its default to match the CompanyIDfk.... I was not aware that you can just change a name in the query field to make that happen like you did it. Nice to know as
I move thought this. Thank you....

Again, thank you all for the assistance
Dave

[Gicu]

You're very welcome Dave!
Cheers,