Originally Posted by
zkrucz
Regarding the "duplicate" fields between Table1 and Table2, they are not really duplicates. Both tables are populated by information coming from our techs using iPads. Table1 reflects quotations. A lot of that information from Table1 is then dispatched to those iPads and then sent back (with additional info) and populates Table2, as work orders (and there may be many work orders against a unique JobNo).
OK, so maybe Table1 is for Quotes and Table2 is for Work Orders per JobNum.
Originally Posted by
zkrucz
So, after all said and done, can the "duplicates" under JobNum in the Account Summary Form be eliminated with an expression?
I was only concerned with trying to get the table structures /relationships in better shape. You still should normalize at tblAccounts, tblTable1 and Table2.
I am not sure where you are headed with the query or what you want to do with it. Remember, you have been working on the dB for a while and we have just seen a minute of it.
Here is the query that works with the attached dB - it is saves as Query1.
Code:
SELECT tblTABLE2.Table2ID_PK, tblTABLE2.Table1ID_FK, tblTABLE2.ActualDate, tblTABLE2.[AccountNum], tblTABLE2.BusinessName, tblTABLE2.[JobNum], tblTABLE2.LABORCOST, tblTABLE2.EQUIPMENTCOST, tblTABLE2.MATERIALSCOST, tblTABLE2.[TOTALJOBCOST], tblTABLE2.[HouseNum], tblTABLE2.StreetName, tblTABLE2.Town, tblTABLE2.[PhoneNum], tblTABLE2.JobType, tblTABLE2.JobDescription, tblTABLE1.TotalQuote, tblTABLE1.LABORCOST AS LABORCOSTTABLE1, tblTABLE1.QUOTATIONCOST, tblTABLE1.EQUIPMENTCOST AS EQUIPMENTCOST_TABLE1, tblTABLE1.MATERIALSCOST AS MATERIALS_COST_TABLE1, tblTABLE1.TOTALJOBCOST AS TOTALJOB_COST_TABLE1, tblTABLE2.ContractorComments, tblTABLE1.DateTimeStamp FROM tblTABLE1 LEFT JOIN tblTABLE2 ON tblTABLE1.[Table1ID_PK] = tblTABLE2.[Table1ID_FK];
Here is the query reformatted for read ability:
Code:
SELECT
tblTABLE2.Table2ID_PK,
tblTABLE2.Table1ID_FK,
tblTABLE2.ActualDate,
tblTABLE2.AccountNum,
tblTABLE2.BusinessName,
tblTABLE2.JobNum,
tblTABLE2.LABORCOST,
tblTABLE2.EQUIPMENTCOST,
tblTABLE2.MATERIALSCOST,
tblTABLE2.TOTALJOBCOST,
tblTABLE2.HouseNum,
tblTABLE2.StreetName,
tblTABLE2.Town,
tblTABLE2.PhoneNum,
tblTABLE2.JobType,
tblTABLE2.JobDescription,
tblTABLE2.ContractorComments,
tblTABLE1.TotalQuote,
tblTABLE1.LABORCOST AS LABORCOSTTABLE1,
tblTABLE1.QUOTATIONCOST,
tblTABLE1.EQUIPMENTCOST AS EQUIPMENTCOST_TABLE1,
tblTABLE1.MATERIALSCOST AS MATERIALS_COST_TABLE1,
tblTABLE1.TOTALJOBCOST AS TOTALJOB_COST_TABLE1,
tblTABLE1.DateTimeStamp
FROM tblTABLE1 LEFT JOIN tblTABLE2 ON tblTABLE1.[Table1ID_PK] = tblTABLE2.[Table1ID_FK];
I converted the query into a Main Form/ Sub Form arraignment - "frmForm1" (and sub form "sfrmForm2").
Still mot sure where you are headed with it.
Maybe you could tell us more about the process re: "techs using iPads". Because of the tables not being normalized and the duplicating of data in Table1 and Table2, you might be in for writing a lot of UDFs.