SQL Problem

[eczlk91]

I have been working on this for three days, looking on the web and even downloading tutorials, Please help!!!

I am trying to get specific date from my tables:

TBL-AirportInfo
TBL-AirportAssessment

I am developing a threat assesments of airports where I assign 10 different factors to an airport. I have figured out how to do this linking the two tables by AirportID, by a one to many relationship.

I now need to pull out Specific data out of the tables:

I tried this in a form with pull down menu's, very unsuccessfully

Airport Identifier
Date of most current assessment
Threat assessment (this is derived from taking the 10 factors and /10, I haven't figured how to do this yet either. I tried the SUM but it is likewise returning an error)
Last Assessment

After I gather the data display it in a report one airfield at a time with a chart dipicting the results.

I tried the SELECT (I am new to this) and the FROM but it keeps coming up with an error.

Just to start I tried:

SELECT Date [TBL-AirportAssessment.Date] FROM TBL-AirportAssessment

It keeps returning an error.

 

[Galaxiom]

You must use the square brackets around any name that includes spaces, special characters and (generally speaking) rerserved words. So the field called Date (a function) and both table names have this problem (space and minus operator).

However I would recommend you change your naming practices. Most expereinced developers just use CamelCase for everything.

Personally I believe the use of Hungarian Notation (particularly in tablenames) is highly overated.

 

[eczlk91]

I will give it a shot, thank you for your time

 

[boblarson]

Personally I believe the use of Hungarian Notation (particularly in tablenames) is highly overated.

I find it very useful. I typically will name my tables with tblTableNameHere or queries qrySomeNameHere - same for forms and reports. It becomes handy when looking through code or SQL to know immediately what objects you are talking about. However I do not use that notation for procedures. I haven't found that to be useful to me much at all so I don't bother. But with the other objects I do because I've had the horrible luck to have had to rework three major databases for a healthcare company and the person who named the objects used nothing to designate anything so it was a lot of trying to look to see what was what and it wasted a lot of time which didn't need to be wasted.

 

[Galaxiom]

I typically will name my tables with tblTableNameHere or queries qrySomeNameHere - same for forms and reports. It becomes handy when looking through code or SQL to know immediately what objects you are talking about.

Access makes little distinction between a reference to a table and a query when used in SQL or as the RecordSource for a Form or Report. In most cases the distinction doesn't matter that much. (Of course that is different when you start refering to the tabledefs and querydefs and their properties the context makes the nature of the object quite clear anyway.)

Since an SQL reference to a Form or Report will include this information in the Collection section of the reference I don't understand the point of duplicating it in the object name.

I choose simple names for tables and longer names that imply a query for queries without actually using the prefix as a designator. I prefer to name so that the query name is related to the main table involved rather than an having the somewhat arbitrary segregation of tables and queries.

I also use A2007 with the Navigation Pane generally showing Tables and Related Objects view so it is easy to see the nature of an object and it's related objects.

I generally use SQL strings as the RecordSource of Forms and Reports so I tend to have few queries anyway. This does has a small speed penalty as Access must generate a new execution plan each time the form is opened but it does save clutter.

However I do not use that notation for procedures. I haven't found that to be useful to me much at all so I don't bother.

In code it is generally quite easy to know the nature of the object from the context. Here I find the extra characters are a distraction. Sensibly named variables suggest their type without having that particular property dominate their name. Moreover this information is readily available from the QuickInfo in the context menu along with a shortcut to its Definition.

Some developers go to great lengths designating listboxes with lst, combos with cbo, labels with lbl etc etc but I find such arbitrary groupings tiresome.

It makes more sense to me to group on functionality. I only wish that Access supported an Intellisense filter to separate objects and properties.

I've had the horrible luck to have had to rework three major databases for a healthcare company and the person who named the objects used nothing to designate anything so it was a lot of trying to look to see what was what and it wasted a lot of time which didn't need to be wasted.

This is a part of design too often overlooked. Developers should always be conscious of those who have to maintain the database after them. I really cringe when I see objects still with the generic name Access assigned to them on creation.

 

[LPurvis]

This debate will almost always come down to personal preference and existing practices. (Which people tend to stick to... though evolution is no bad thing usually - I for one enjoy not being an amoeba ;-)

At the heart of that site the one line:
>> "Developers should always be conscious of those who have to maintain the database after them"
If there's any likelihood that subsequent developers will be involved in a project (it's always a possibility - sadly none of us are everlasting) then creating and using your own overly esoteric naming convention doesn't serve that at all.
It could be argued that if there is a generally accepted standard - then the further you position yourself from that, you also wander into that territory of making things tougher.
Although I do, I don't feel there is any huge need to prefix table names. Longer names (in queries as a result or not) are something I avoid where possible, though excessive abbreviation should really be avoided even more so (I hate coming in to projects to find tables like WCsOdsFxMth which perhaps meant a great deal to the original developer but says nothing to others).

On the comment
>> "I generally use SQL strings as the RecordSource of Forms and Reports so I tend to have few queries anyway."
Are you talking about ad hoc SQL strings assigned at runtime, filtering a form? Or some persistently assigned SQL statement recordsource properties (that are perhaps filtered)?

>> "Some developers go to great lengths designating listboxes with lst, combos with cbo, labels with lbl etc etc but I find such arbitrary groupings tiresome."
If they're bound (or unbound data displaying) controls then the prefix differing from the field name may help with disambiguation - but there's no real need for that if you know what you're doing. But that prefix is a PITA for me personally. It's extra development time to implement and doesn't really offer me much.
Though I do so name unbound controls that serve some other, functional, purpose.

But each to their own. And as I say, I try to not make my own standards set in stone. Improving and just accepting change generally is a good ability to maintain.

 

[boblarson]

Okay, I'll give a real-life example. Can you tell me just by looking at this SQL which are tables and which are queries? And by the way, I needed to know which in order to go check the queries when I needed to make a change on the way that the dates of the quarter were calculated (because one fund has to shift forward one because of the different fiscal year than everyone else). There is no need to check the tables but I would need to check the queries and if I don't know which is which I have to search in the queries to see if the object exists in order to check it. Now I didn't write this and this does have qry and tbl on the front of them so it was very simple to find the ones I needed to look at while ignoring the tables. I am posting this but I'm removing the prefixes so you can tell me which is which. I'll post the actual SQL a bit later after you've had a chance to look at it.

INSERT INTO KeyCharScenarioDetails ( partner_id, property_id, lt_name, City_State, state_code, county, GPProfit, GPNewRepeat, Property_Type, Tenant_Mix, units, MktUnits, TotalDevCost, TtlDevCostUnit, DealStat, Hard, Soft, WAIntRate, AvgBedrooms, AvgSqFt, RentPerSqFt, MktRentAdvantage, RepResPerUnit, DCR, UTE, LTE, OrigCapPNC, TCType, PricePerCredit, RentalAsst, ScenarioID )
SELECT ProfilePortfolio.PortfolioID, pselPropertyNarrow.property_id, pselPropertyNarrow.lt_name, ([pselPropertyNarrow]![Property_city]) & ", " & Trim([pselPropertyNarrow]![state_code]) AS City_State, pselPropertyNarrow.state_code, pselPropertyNarrow.county, IIf([NonProfit]=True,"Non-Profit","Profit") AS GPProfit, IIf([pselPropertyNarrow]![New_GP]=-1,"New","Repeat") AS GPNewRepeat, pselPropertyNarrow.Property_Type, pselPropertyNarrow.Tenant_Mix, pselPropertyNarrow.units, pselPropertyNarrow.MktUnits, TransSourceUse.TotalDevCost, IIf(IsNull([Units]) Or IsNull([TotalDevCost]),Null,CCur(([TotalDevCost]/[units]))) AS TtlDevCostUnit, IIf([Deal_Status]="admitted","Admitted",IIf([Deal_Status]="> 75% likely to sign","LOI Pending",IIf([Deal_Status]="< 75% likely to sign","LOI Pending",IIf([Deal_Status]="LOI signed > 60 days to close","LOI",IIf([Deal_Status]="LOI signed < 60 days to close","LOI",IIf([Deal_Status]="Short Form Admitted","LOI",0)))))) AS DealStat, KeyCharPermHardSoft.Hard, KeyCharPermHardSoft.Soft, KeyCharPermDebtsHard.WAIntRate, KeyCharUnitSumm.AvgBedrooms, KeyCharUnitSumm.AvgSqFt, KeyCharUnitSumm.RentPerSqFt, KeyCharUnitSumm.MktRentAdvantage, [Annual_Deposite_Actual]/[units] AS RepResPerUnit, KeyChar.StabDCR AS DCR, KeyChar.UTCapital AS UTE, IIf([KeyChar].[LTCapital]=0,[KeyCharCap].[TotAmt],[KeyChar].[LTCapital]) AS LTE, KeyCharCap.OrigCapPNC, KeyChar.CreditType AS TCType, KeyCharTaxCredits.PricePerCredit, SubsidizedUnits.Subsidized AS RentalAsst, [Forms]![frmMain]![sbfrmKeyCharacteristicsScenarios].[Form]![txtScenarioID] AS ScenarioID
FROM ( ProfilePortfolio INNER JOIN PortfolioProps ON ProfilePortfolio.PortfolioID = PortfolioProps.PortfolioFK) INNER JOIN (((KeyChar RIGHT JOIN (((((((( pselPropertyNarrow LEFT JOIN wselFiscPerfMaster ON pselPropertyNarrow.property_id = wselFiscPerfMaster.property_id) LEFT JOIN TransSourceUse ON pselPropertyNarrow.property_id = TransSourceUse.PropertyFK) LEFT JOIN KeyCharPermDebtsHard ON pselPropertyNarrow.property_id = KeyCharPermDebtsHard.PropertyFK) LEFT JOIN ExternalDevTeam ON pselPropertyNarrow.property_id = ExternalDevTeam.PropertyFK) LEFT JOIN TransDevTeamGPs ON ExternalDevTeam.GPOrgFK = TransDevTeamGPs.DTGenPtpFK) LEFT JOIN KeyCharTaxCredits ON pselPropertyNarrow.property_id = KeyCharTaxCredits.PropertyFK) LEFT JOIN KeyCharUnitSumm ON pselPropertyNarrow.property_id = KeyCharUnitSumm.Property_ID) LEFT JOIN KeyCharPermHardSoft ON pselPropertyNarrow.property_id = KeyCharPermHardSoft.PropertyFK) ON KeyChar.PropertyID = pselPropertyNarrow.property_id) LEFT JOIN KeyCharCap ON pselPropertyNarrow.property_id = KeyCharCap.Property_ID) LEFT JOIN SubsidizedUnits ON pselPropertyNarrow.property_id = SubsidizedUnits.property_id) ON PortfolioProps.PropertyFK = pselPropertyNarrow.property_id
WHERE ((( ProfilePortfolio.PortfolioID)=[Forms]![frmMain]![cboUpperTierID]) AND (( pselPropertyNarrow.property_id)=[Forms]![frmMain]![lstPropPorts]))
ORDER BY ProfilePortfolio.PortfolioID DESC;

So, you're telling me that you would rather wade through 13 tables/queries, checking out which is which, instead of just knowing where to go look by the name? If so, more power to you, but I don't. I want to keep from wasting my time looking for something that isn't there.

And this IS a real example of some of what we have. I'll post a Union Query, that whoever made this thing, did in VBA which would make your head hurt even with the naming convention. But troubleshooting that one? EEk!

 

[boblarson]

And here's the Union Query - so where do you look for any problems with queries (and not having to deal with tables)?

strSQL1 = "SELECT ProfilePortfolio.PortfolioID, "
strSQL1 = strSQL1 & "pselPropertyNarrow.property_id, "
strSQL1 = strSQL1 & "pselPropertyNarrow.lt_name, IIf(Year("
strSQL1 = strSQL1 & "[ComplPerExp])<[PeriodYear],'Post Compliance',"
strSQL1 = strSQL1 & "[Status]) AS Prop_Status, PrimContact."
strSQL1 = strSQL1 & "FullName AS MainContact, PrimContact."
strSQL1 = strSQL1 & "MainContactID, dscr.DSCR, Occ."
strSQL1 = strSQL1 & "PhysOccPct, Occ.TaxCrOccPct, PropExcluded."
strSQL1 = strSQL1 & "property_id AS Excluded, Watchlist."
strSQL1 = strSQL1 & "property_id AS WatchList, GetFormYear"
strSQL1 = strSQL1 & "('Switchboard') AS PeriodYear "
strSQL1 = strSQL1 & "FROM (((((ProfilePortfolio INNER JOIN "
strSQL1 = strSQL1 & "PortfolioProps ON ProfilePortfolio."
strSQL1 = strSQL1 & "PortfolioID = PortfolioProps.PortfolioFK) "
strSQL1 = strSQL1 & "INNER JOIN (((pselPropertyNarrow LEFT JOIN "
strSQL1 = strSQL1 & "PrimContact ON pselPropertyNarrow."
strSQL1 = strSQL1 & "property_id = PrimContact.PropertyFK) LEFT "
strSQL1 = strSQL1 & "JOIN dscr ON pselPropertyNarrow."
strSQL1 = strSQL1 & "property_id = dscr.property_id) LEFT JOIN "
strSQL1 = strSQL1 & "Watchlist ON pselPropertyNarrow."
strSQL1 = strSQL1 & "property_id = Watchlist.property_id) ON "
strSQL1 = strSQL1 & "PortfolioProps.PropertyFK = "
strSQL1 = strSQL1 & "pselPropertyNarrow.property_id) LEFT JOIN "
strSQL1 = strSQL1 & "PropExcluded ON (PortfolioProps."
strSQL1 = strSQL1 & "PortfolioFK = PropExcluded.PortfolioID) AND "
strSQL1 = strSQL1 & "(PortfolioProps.PropertyFK = "
strSQL1 = strSQL1 & "PropExcluded.property_id)) LEFT JOIN "
strSQL1 = strSQL1 & "TransProp ON pselPropertyNarrow."
strSQL1 = strSQL1 & "property_id = TransProp.PropertyFK) LEFT "
strSQL1 = strSQL1 & "JOIN TransPropUnitMgt ON "
strSQL1 = strSQL1 & "pselPropertyNarrow.property_id = "
strSQL1 = strSQL1 & "TransPropUnitMgt.PropertyFK) LEFT JOIN "
strSQL1 = strSQL1 & "Occ ON pselPropertyNarrow.property_id = "
strSQL1 = strSQL1 & "Occ.PropertyFK "
strSQL1 = strSQL1 & "WHERE (((ProfilePortfolio.PortfolioID) Not In "
strSQL1 = strSQL1 & "(1,104,163)) AND ((PortfolioProps.Actual)<"
strSQL1 = strSQL1 & ">0) AND ((PortfolioProps.AdmitDate) Is "
strSQL1 = strSQL1 & "Not Null) AND ((PortfolioProps.ExitDate) "
strSQL1 = strSQL1 & "Is Null) AND ((IsNull([pselPropertyNarrow]."
strSQL1 = strSQL1 & "[property_id]))=False)) OR (("
strSQL1 = strSQL1 & "(ProfilePortfolio.PortfolioID) Not In (1,104,"
strSQL1 = strSQL1 & "163)) AND ((PortfolioProps.Actual)<>0) "
strSQL1 = strSQL1 & "AND ((PortfolioProps.AdmitDate) Is Not "
strSQL1 = strSQL1 & "Null) AND ((IsNull([pselPropertyNarrow]."
strSQL1 = strSQL1 & "[property_id]))=False) AND ((DatePart('yyyy',"
strSQL1 = strSQL1 & "[exitdate])*100+DatePart('q',[exitdate]))>="
strSQL1 = strSQL1 & "GetFormYear('Switchboard')*100+GetFormquarter"
strSQL1 = strSQL1 & "('Switchboard')))"
strSQL2 = "SELECT ParentChildUTs.ParentUT AS "
strSQL2 = strSQL2 & "PortfolioID, pselPropertyNarrow.property_id, "
strSQL2 = strSQL2 & "pselPropertyNarrow.lt_name AS lt_name, IIf"
strSQL2 = strSQL2 & "(Year([ComplPerExp])<[PeriodYear],'Post "
strSQL2 = strSQL2 & "Compliance',[Status]) AS Prop_Status, "
strSQL2 = strSQL2 & "PrimContact.FullName AS MainContact, "
strSQL2 = strSQL2 & "PrimContact.MainContactID, dscr.DSCR, "
strSQL2 = strSQL2 & "Occ.PhysOccPct, Occ.TaxCrOccPct, "
strSQL2 = strSQL2 & "PropExcluded.property_id AS Excluded, "
strSQL2 = strSQL2 & "Watchlist.property_id AS WatchList, "
strSQL2 = strSQL2 & "GetFormYear('Switchboard') AS PeriodYear "
strSQL2 = strSQL2 & "FROM ((((((ProfilePortfolio INNER JOIN "
strSQL2 = strSQL2 & "ParentChildUTs ON ProfilePortfolio."
strSQL2 = strSQL2 & "PortfolioID = ParentChildUTs.ChildUT) "
strSQL2 = strSQL2 & "INNER JOIN PortfolioProps ON "
strSQL2 = strSQL2 & "ProfilePortfolio.PortfolioID = "
strSQL2 = strSQL2 & "PortfolioProps.PortfolioFK) INNER JOIN (("
strSQL2 = strSQL2 & "(pselPropertyNarrow LEFT JOIN "
strSQL2 = strSQL2 & "PrimContact ON pselPropertyNarrow."
strSQL2 = strSQL2 & "property_id = PrimContact.PropertyFK) LEFT "
strSQL2 = strSQL2 & "JOIN dscr ON pselPropertyNarrow."
strSQL2 = strSQL2 & "property_id = dscr.property_id) LEFT JOIN "
strSQL2 = strSQL2 & "Watchlist ON pselPropertyNarrow."
strSQL2 = strSQL2 & "property_id = Watchlist.property_id) ON "
strSQL2 = strSQL2 & "PortfolioProps.PropertyFK = "
strSQL2 = strSQL2 & "pselPropertyNarrow.property_id) LEFT JOIN "
strSQL2 = strSQL2 & "(PropParent LEFT JOIN PropExcluded ON "
strSQL2 = strSQL2 & "(PropParent.ParentID = PropExcluded."
strSQL2 = strSQL2 & "PortfolioID) AND (PropParent.property_id = "
strSQL2 = strSQL2 & "PropExcluded.property_id)) ON "
strSQL2 = strSQL2 & "(PortfolioProps.PropertyFK = "
strSQL2 = strSQL2 & "PropParent.property_id) AND "
strSQL2 = strSQL2 & "(PortfolioProps.PortfolioFK = "
strSQL2 = strSQL2 & "PropParent.PortfolioID)) LEFT JOIN "
strSQL2 = strSQL2 & "TransProp ON pselPropertyNarrow."
strSQL2 = strSQL2 & "property_id = TransProp.PropertyFK) LEFT "
strSQL2 = strSQL2 & "JOIN TransPropUnitMgt ON "
strSQL2 = strSQL2 & "pselPropertyNarrow.property_id = "
strSQL2 = strSQL2 & "TransPropUnitMgt.PropertyFK) LEFT JOIN "
strSQL2 = strSQL2 & "Occ ON pselPropertyNarrow.property_id = "
strSQL2 = strSQL2 & "Occ.PropertyFK "
strSQL2 = strSQL2 & "WHERE ((([ParentUT]=[ParentID])=True)) "
strSQL2 = strSQL2 & "ORDER BY ProfilePortfolio.PortfolioID, "
strSQL2 = strSQL2 & "lt_name;"

And the real one with the prefixes:

strSQL1 = "SELECT dbo_ProfilePortfolio.PortfolioID, "
strSQL1 = strSQL1 & "dbo_pselPropertyNarrow.property_id, "
strSQL1 = strSQL1 & "dbo_pselPropertyNarrow.lt_name, IIf(Year("
strSQL1 = strSQL1 & "[ComplPerExp])<[PeriodYear],'Post Compliance',"
strSQL1 = strSQL1 & "[Status]) AS Prop_Status, sbqryPrimContact."
strSQL1 = strSQL1 & "FullName AS MainContact, sbqryPrimContact."
strSQL1 = strSQL1 & "MainContactID, sbqrydscr.DSCR, sbqryOcc."
strSQL1 = strSQL1 & "PhysOccPct, sbqryOcc.TaxCrOccPct, tblPropExcluded."
strSQL1 = strSQL1 & "property_id AS Excluded, sbqryWatchlist."
strSQL1 = strSQL1 & "property_id AS WatchList, GetFormYear"
strSQL1 = strSQL1 & "('Switchboard') AS PeriodYear "
strSQL1 = strSQL1 & "FROM (((((dbo_ProfilePortfolio INNER JOIN "
strSQL1 = strSQL1 & "dbo_XrefPortfolioProps ON dbo_ProfilePortfolio."
strSQL1 = strSQL1 & "PortfolioID = dbo_XrefPortfolioProps.PortfolioFK) "
strSQL1 = strSQL1 & "INNER JOIN (((dbo_pselPropertyNarrow LEFT JOIN "
strSQL1 = strSQL1 & "sbqryPrimContact ON dbo_pselPropertyNarrow."
strSQL1 = strSQL1 & "property_id = sbqryPrimContact.PropertyFK) LEFT "
strSQL1 = strSQL1 & "JOIN sbqrydscr ON dbo_pselPropertyNarrow."
strSQL1 = strSQL1 & "property_id = sbqrydscr.property_id) LEFT JOIN "
strSQL1 = strSQL1 & "sbqryWatchlist ON dbo_pselPropertyNarrow."
strSQL1 = strSQL1 & "property_id = sbqryWatchlist.property_id) ON "
strSQL1 = strSQL1 & "dbo_XrefPortfolioProps.PropertyFK = "
strSQL1 = strSQL1 & "dbo_pselPropertyNarrow.property_id) LEFT JOIN "
strSQL1 = strSQL1 & "tblPropExcluded ON (dbo_XrefPortfolioProps."
strSQL1 = strSQL1 & "PortfolioFK = tblPropExcluded.PortfolioID) AND "
strSQL1 = strSQL1 & "(dbo_XrefPortfolioProps.PropertyFK = "
strSQL1 = strSQL1 & "tblPropExcluded.property_id)) LEFT JOIN "
strSQL1 = strSQL1 & "sbqryTransProp ON dbo_pselPropertyNarrow."
strSQL1 = strSQL1 & "property_id = sbqryTransProp.PropertyFK) LEFT "
strSQL1 = strSQL1 & "JOIN dbo_TransPropUnitMgt ON "
strSQL1 = strSQL1 & "dbo_pselPropertyNarrow.property_id = "
strSQL1 = strSQL1 & "dbo_TransPropUnitMgt.PropertyFK) LEFT JOIN "
strSQL1 = strSQL1 & "sbqryOcc ON dbo_pselPropertyNarrow.property_id = "
strSQL1 = strSQL1 & "sbqryOcc.PropertyFK "
strSQL1 = strSQL1 & "WHERE (((dbo_ProfilePortfolio.PortfolioID) Not In "
strSQL1 = strSQL1 & "(1,104,163)) AND ((dbo_XrefPortfolioProps.Actual)<"
strSQL1 = strSQL1 & ">0) AND ((dbo_XrefPortfolioProps.AdmitDate) Is "
strSQL1 = strSQL1 & "Not Null) AND ((dbo_XrefPortfolioProps.ExitDate) "
strSQL1 = strSQL1 & "Is Null) AND ((IsNull([dbo_pselPropertyNarrow]."
strSQL1 = strSQL1 & "[property_id]))=False)) OR (("
strSQL1 = strSQL1 & "(dbo_ProfilePortfolio.PortfolioID) Not In (1,104,"
strSQL1 = strSQL1 & "163)) AND ((dbo_XrefPortfolioProps.Actual)<>0) "
strSQL1 = strSQL1 & "AND ((dbo_XrefPortfolioProps.AdmitDate) Is Not "
strSQL1 = strSQL1 & "Null) AND ((IsNull([dbo_pselPropertyNarrow]."
strSQL1 = strSQL1 & "[property_id]))=False) AND ((DatePart('yyyy',"
strSQL1 = strSQL1 & "[exitdate])*100+DatePart('q',[exitdate]))>="
strSQL1 = strSQL1 & "GetFormYear('Switchboard')*100+GetFormquarter"
strSQL1 = strSQL1 & "('Switchboard')))"
strSQL2 = "SELECT dbo_XrefParentChildUTs.ParentUT AS "
strSQL2 = strSQL2 & "PortfolioID, dbo_pselPropertyNarrow.property_id, "
strSQL2 = strSQL2 & "dbo_pselPropertyNarrow.lt_name AS lt_name, IIf"
strSQL2 = strSQL2 & "(Year([ComplPerExp])<[PeriodYear],'Post "
strSQL2 = strSQL2 & "Compliance',[Status]) AS Prop_Status, "
strSQL2 = strSQL2 & "sbqryPrimContact.FullName AS MainContact, "
strSQL2 = strSQL2 & "sbqryPrimContact.MainContactID, sbqrydscr.DSCR, "
strSQL2 = strSQL2 & "sbqryOcc.PhysOccPct, sbqryOcc.TaxCrOccPct, "
strSQL2 = strSQL2 & "tblPropExcluded.property_id AS Excluded, "
strSQL2 = strSQL2 & "sbqryWatchlist.property_id AS WatchList, "
strSQL2 = strSQL2 & "GetFormYear('Switchboard') AS PeriodYear "
strSQL2 = strSQL2 & "FROM ((((((dbo_ProfilePortfolio INNER JOIN "
strSQL2 = strSQL2 & "dbo_XrefParentChildUTs ON dbo_ProfilePortfolio."
strSQL2 = strSQL2 & "PortfolioID = dbo_XrefParentChildUTs.ChildUT) "
strSQL2 = strSQL2 & "INNER JOIN dbo_XrefPortfolioProps ON "
strSQL2 = strSQL2 & "dbo_ProfilePortfolio.PortfolioID = "
strSQL2 = strSQL2 & "dbo_XrefPortfolioProps.PortfolioFK) INNER JOIN (("
strSQL2 = strSQL2 & "(dbo_pselPropertyNarrow LEFT JOIN "
strSQL2 = strSQL2 & "sbqryPrimContact ON dbo_pselPropertyNarrow."
strSQL2 = strSQL2 & "property_id = sbqryPrimContact.PropertyFK) LEFT "
strSQL2 = strSQL2 & "JOIN sbqrydscr ON dbo_pselPropertyNarrow."
strSQL2 = strSQL2 & "property_id = sbqrydscr.property_id) LEFT JOIN "
strSQL2 = strSQL2 & "sbqryWatchlist ON dbo_pselPropertyNarrow."
strSQL2 = strSQL2 & "property_id = sbqryWatchlist.property_id) ON "
strSQL2 = strSQL2 & "dbo_XrefPortfolioProps.PropertyFK = "
strSQL2 = strSQL2 & "dbo_pselPropertyNarrow.property_id) LEFT JOIN "
strSQL2 = strSQL2 & "(sbqryPropParent LEFT JOIN tblPropExcluded ON "
strSQL2 = strSQL2 & "(sbqryPropParent.ParentID = tblPropExcluded."
strSQL2 = strSQL2 & "PortfolioID) AND (sbqryPropParent.property_id = "
strSQL2 = strSQL2 & "tblPropExcluded.property_id)) ON "
strSQL2 = strSQL2 & "(dbo_XrefPortfolioProps.PropertyFK = "
strSQL2 = strSQL2 & "sbqryPropParent.property_id) AND "
strSQL2 = strSQL2 & "(dbo_XrefPortfolioProps.PortfolioFK = "
strSQL2 = strSQL2 & "sbqryPropParent.PortfolioID)) LEFT JOIN "
strSQL2 = strSQL2 & "sbqryTransProp ON dbo_pselPropertyNarrow."
strSQL2 = strSQL2 & "property_id = sbqryTransProp.PropertyFK) LEFT "
strSQL2 = strSQL2 & "JOIN dbo_TransPropUnitMgt ON "
strSQL2 = strSQL2 & "dbo_pselPropertyNarrow.property_id = "
strSQL2 = strSQL2 & "dbo_TransPropUnitMgt.PropertyFK) LEFT JOIN "
strSQL2 = strSQL2 & "sbqryOcc ON dbo_pselPropertyNarrow.property_id = "
strSQL2 = strSQL2 & "sbqryOcc.PropertyFK "
strSQL2 = strSQL2 & "WHERE ((([ParentUT]=[ParentID])=True)) "
strSQL2 = strSQL2 & "ORDER BY dbo_ProfilePortfolio.PortfolioID, "
strSQL2 = strSQL2 & "lt_name;"

 

[eczlk91]

I didn't think i would stir up so much controversy. I truly appreciate all the input and it has given me a good chance to see all sides of the issue.

I still do have one question though:

In a form what is the best way, using a combo box, to select specific data from two different table that are joined?

I want to selct an airport then select a specific date of an inspection of that airport.

Once again I truly appreciate the feedback.

 

[Galaxiom]

Okay, I'll give a real-life example. Can you tell me just by looking at this SQL which are tables and which are queries

So, you're telling me that you would rather wade through 13 tables/queries, checking out which is which, instead of just knowing where to go look by the name??

Try using a boat.

The navigation pane in A2007 has a very useful Search Bar. It filters as you type. Turn it on in the Navigation Bar context menu.

 

[boblarson]

Try using a boat.

The navigation pane in A2007 has a very useful Search Bar. It filters as you type. Turn it on in the Navigation Bar context menu.

And if we ever get to 2007 or 2010 where I work (we're on 2003) that would probably prove useful. However, it would still potentially require the search for each table whereas I can immediately discount several of the ones in the list and focus directly on the queries.

So, I'm happy for you that you aren't bothered by having to go try to decipher someone else's work when there are a lot of objects. It bothers me however and therefore I will do what I can to ensure that I, as well as anyone who comes after me, will be able to see immediately, without having to do any searching or tests, what is what. Perhaps you've not had to wade through a huge mess which had no consistent naming pattern and your boss expecting results quickly. Maybe you have, but I have definitely experienced that and I didn't like it.

So I guess we won't agree on this point at all.