Join not working.

[jlockley]

Trying to merge data from table two (cardscanstep2) with 1200 records into differently structured empty table one (roladex imports) produces an empty table 3 (answer.db, if you will).


Where's the glitch, if anyone would be so kind.

Here's the whole mess.

Query
ANSWER: RIV:ANSWER.DB

Export Import\cardscanstep2.DB | Title | Company | City | Zip |
| _join3 | _join4 | _join5 | _join |

Export Import\cardscanstep2.DB | Whole | Work Phone |
| Check _join1 | _join2 |

Export Import\roladexforimports.db | NAME | LOCATION |
| Check _join1 | Check _join4 |

Export Import\roladexforimports.db | TITLE | PARTNER | DATE |
| Check _join3 | Check | Check |

Export Import\roladexforimports.db | TELEPHONE | FAX | Wphone |
| Check | Check | Check _join2 |

Export Import\roladexforimports.db | Cell-pgr | E-MAIL | Address |
| Check | Check | Check |

Export Import\roladexforimports.db | CITY | STATE | ZIP |
| Check _join5 | Check | Check _join6 |

Export Import\roladexforimports.db | Country | INFORMATION |
| Check | Check |

Export Import\roladexforimports.db | PREFERRED_LOCATION | SALARY | Type |
| Check | Check | Check |


EndQuery

 

[TonyMcGuire]

Without '!' on any of your joins, if one table would result in no records being returned then the whole answer table will be empty.

So if you have an empty table in that query, as you say, you will get no records in your answer table.

Tony McGuire

http://forums.tonymcguire.com

 

[jlockley]

'!' ? I tried this three ways, one using the join icon, second entering join and third just entering a common term for the fields in both. Where does '!' fit in. Thanks! (another '!')

 

[jlockley]

I thought I had this down,but I am definitely missing something. What am I missing here in my attempts to bring records of b.db into differently structured a.db?
Table A
Size A 24
Color A 12

table B
Dimension A 10
Hue A 10

Table B has 4 records wich I would Like to incorporate in Table A with Table A's field structure.

I thought I could do this: (all checked and unchecked variations explored)
Query
ANSWER: RIV:ANSWER.DB

b.db | Dimension | Hue |
| Check _join1 | Check _join2 |

a.db | Size | Color |
| Check _join1 | Check _join2 |

EndQuery

which begets an empty this
Dimension A 10
Hue A 10
Size A 24
Color A 12

using a .. wildcard in query view does nothing. Am I trying to do something which is impossible?

 

[beanbrain]

It looks like the join between table "Import\Cardscanstep2.db and Export Import\roladexforimports.db at field "Zip" has an error.

The _join in the first table at Zip does not match the _join6 in the second at Zip.

To clarify:
[tt]
Export Import\cardscanstep2.DB | Title | Company | City | Zip |
| _join3 | _join4 | _join5 | _join |

Export Import\roladexforimports.db | CITY | STATE | ZIP |
| Check _join5 | Check | Check _join6 |
[/tt]

 

[jlockley]

Thanks. It works in the meantime - I used the query view with the "import" option. (Duh). I can't see where that is noted in the query itself, however. I guess it doesn't matter.

Query
ANSWER: :WORK:Export Import\Cardscan\imported cardscan.db

Export Import\Cardscan\roladexforimports.db | NAME | LOCATION |
| Check _join12 | Check _join2 |

Export Import\Cardscan\roladexforimports.db | TITLE | PARTNER |
| Check _join1 | Check |

Export Import\Cardscan\roladexforimports.db | DATE | TELEPHONE |
| Check _join11 | Check |

Export Import\Cardscan\roladexforimports.db | FAX | Wphone |
| Check | Check _join13 |

Export Import\Cardscan\roladexforimports.db | Cell-pgr | E-MAIL |
| Check _join14 | Check _join8 |

Export Import\Cardscan\roladexforimports.db | Address | CITY |
| Check _join3 | Check _join4 |

Export Import\Cardscan\roladexforimports.db | STATE | ZIP |
| Check _join5 | Check _join6 |

Export Import\Cardscan\roladexforimports.db | Country | INFORMATION |
| Check _join7 | Check _join9 |

Export Import\Cardscan\roladexforimports.db | PREFERRED_LOCATION | SALARY |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Type | Rating backg |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Rating Prof | Rating stab |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Rating food | Rating org-sys |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Rating vol | Rating attitude |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Restaurant | REST-S | R-VOL |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | R-STAR | R-CORP | CLUB |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | CL-TOP | HOT | HOT-TOP |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Inn-Bed Breakfast | Maritime |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | HOT UNIT | R&d | COMM |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | CTG | FS | DELI/HRM |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Chain | Quick Serve | Retail |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | BAKERY RTL | Bakery Comm. |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | BKRY-WHLS | DOM | QUAL |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | SEAT NR | BNKT NR | VOL |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | TRAIN | SPECIAL_SKILLS |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | French - Haute |
| Check |

Export Import\Cardscan\roladexforimports.db | French - Bistro | Continental |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Italian | Spanish | Mexican |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Other Hispanic | Haute |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Mediterranean | Chinese |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Japanese | Indian | Other Asian |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Other Exotic | Island/Carribean |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | American Contemporary |
| Check |

Export Import\Cardscan\roladexforimports.db | American Comfort | Steakhouse |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Seafood | Other Product |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Light | Light 2 | Calif Style |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Vegetarian | American Bistro |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Bar B Que | Garde | Ice |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Show Pieces | Other Skill |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Sugar | Chocolate | Cake deco |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Vienoiserie | Fine Desserts |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | California Style | Cajun |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Other American regional |
| Check |

Export Import\Cardscan\roladexforimports.db | Buffet | Banquet | Pastry |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Desserts | Bread |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Volume Production |
| Check |

Export Import\Cardscan\roladexforimports.db | High Profile | Food Chemistry |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Nutrition | Trainer | Haccp |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Instructor | Computer |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Multi Unit | Opening/Planning |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | CEC | Srve Safe | Speak Span |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Press Portfolio | Awards |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | CorpChef | ExecChef | WCh |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | ExSch | Sch |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Chef and Manager | Garde Manger |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Tournant | ChdC | KM |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | PstryCh | PstryAsst | Baker |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Dir Bkry Prod | BnktCh |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | BnktAsst | LnCk | DFB |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Dir Ops-CEO | Dir Prod | CorpGM |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | General Manager | Manager |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | AGM | Dir/Ctg | CatMgr |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Bnktmgr | Retail management |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Other management | Wine |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Resume | Resumememo | Notes |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Quality Of Life |
| Check |

Export Import\Cardscan\roladexforimports.db | Move from SF or CA | Non Urban |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Print menu or pic |
| Check |

Export Import\Cardscan\roladexforimports.db | Reference Letter | References |
| Check | Check |

Export Import\Cardscan\roladexforimports.db | Menu | Source | Consider |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Do | Picture | Foodpics |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Pic1 | Pic2 | Pic3 |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Pic4 | Pic5 | Pic6 |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Pic7 | Pic8 | Pic9 |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Pic10 | Photo | Review |
| Check | Check | Check |

Export Import\Cardscan\roladexforimports.db | Rank | Hide | Categories |
| Check | Check | Check _join10 |

Export Import\Cardscan\cardscanstep2.DB | Title | Company | Address | City |
Insert | _join1 | _join2 | _join3 | _join4 |

Export Import\Cardscan\cardscanstep2.DB | State | Zip_Code | Country |
| _join5 | _join6 | _join7 |

Export Import\Cardscan\cardscanstep2.DB | Email | Notes | Categories |
| _join8 | _join9 | _join10 |

Export Import\Cardscan\cardscanstep2.DB | Modified_Date | Name |
| _join11 | _join12 |

Export Import\Cardscan\cardscanstep2.DB | Work Phone | Mobile |
| _join13 | _join14 |

EndQuery

 

[hcts]

Why not create the table and then use a tcursor scan to properly place the records into the answer table? That way you can check/validate that the data is saved into the answer table. Here is an example from one of my apps.

First I create the answer table with the data settings I want:


T = CREATE "RIV:ANSWER.DB"
WITH "KEY" : "A45" ,
"JOB#" : "A6" ,
"PRINTSEQ" : "N" ,
"AREA1" : "A2" ,
"AREA2" : "A2" ,
"ITEM#" : "A4" ,
"SUBITEM#" : "A4" ,
"PRINTCNT" : "N" ,
"DESCRIPTION" : "M200",
"DESCRIPTIONB" : "M200",
"UNIT" : "A4" ,
"EXTENDED" : "A1" ,
"ESTAMT" : "N" ,
"PRVAMT" : "N" ,
"WIPAMT" : "N" ,
"WIPPCT" : "N" ,
"WIPRET" : "N" ,
"CURAMT" : "N" ,
"JTDAMT" : "N" ,
"UCSTPCT" : "N" ,
"UPRICE" : "N" ,
"TOTAMT" : "A15" ,
"TOTAMTB" : "A15" ,
"RETPCT" : "N" ,
"RETAMT" : "N" ,
"PUSER" : "A45" ,
"JOBD" : "A45"
KEY "KEY"

ENDCREATE

Then I query out the data to another table and scan that to enter in the relevant data for the answer table.

TC1.OPEN("RIV:ANSWER.DB")
TC2.OPEN("RIV:__BLITEM.DB")
TC1.EDIT()
SCAN TC2 :
TC1.INSERTRECORD()
I1 = TC2.ITEM#
A1 = I1
S1 = FORMAT("w4.0,EZ,AR",A1)
TC1."KEY" = TC2.AREA1+S1+TC2.SUBITEM#
TC1."JOB#" = TC2."JOB#"
TC1."PRINTSEQ" = TC2."PRINTSEQ"
TC1."AREA1" = TC2."AREA1"
TC1."AREA2" = TC2."AREA2"
TC1."ITEM#" = S1 ;TC2."ITEM#"
TC1."SUBITEM#" = TC2."SUBITEM#"
TC1."PRINTCNT" = TC2."PRINTCNT"
TC1."DESCRIPTION"= TC2."DESCRIPTION"
TC1."UNIT" = TC2."UNIT"
TC1."EXTENDED" = TC2."EXTENDED"
TC1."ESTAMT" = TC2."ESTAMT"
TC1."PRVAMT" = TC2."PRVAMT"
TC1."WIPAMT" = TC2."WIPAMT"
TC1."WIPPCT" = TC2."WIPPCT"
TC1."WIPRET" = TC2."WIPRET"
TC1."CURAMT" = TC2."CURAMT"
TC1."JTDAMT" = TC2."JTDAMT"
TC1."UCSTPCT" = TC2."UCSTPCT"
TC1."UPRICE" = TC2."UPRICE"
TC1."TOTAMT" = ROUND(TC2."TOTAMT",2)
TC1."RETPCT" = TC2."RETPCT"
TC1."RETAMT" = TC2."RETAMT"
TC1."PUSER" = USER ;TC2."PUSER"
TC1."JOBD" = JOBD ;TC2."JOBD"
IF
TC1.POSTRECORD()
MESSAGE(STRING(I1)+" records saved...")
ELSE
MSGSTOP("RECORD FAILED VALIDATION","Record # "+string(tc1.recno())+ " failed to validate into the answer table.")
ENDIF
ENDSCAN
TC1.ENDEDIT()
TC2.CLOSE()
TC1.CLOSE()

Now I have a bunch of other code later that creates subtotals and sorts the data in a particular way for this billing program, but the basic premise is the same, with TCursors you can control and validate your data as it is saved to the answer table. This give you more control.

Hope this helps

James D. Howard

 

[jlockley]

I haven't done much with t cursors (pretty primitive in using Paradox even after so many years) but I like the idea. I will fiddle with it over the weekend. Thanks.