Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

simple qbe poser 1

Status
Not open for further replies.
jlockley

jlockley

Technical User
Nov 28, 2001
1,522
US
No matter how I twist it, I get the wrong outcome.

I have table "numbers" and table "letters", each with fields af, bf, cf, df . They are keyed on field a

the records in "numbers" are
a 1 1 1
b 2 2 2
c 3 3 3
d 4 4 4

The records in "letters" are
a a a a
b b b b
c c c c
d d d d

I need the query result to read
a 1 1 a
b 2 2 b
c 3 3 c
d 4 4 d

If I query with join elements

Query
ANSWER: :pRIV:ANSWER.DB

tests\numbers.db | Bf | Cf |
Insert | _join2 | _join3 |

tests\letters.db | Af | Bf | Cf | Df |
| Check | Check _join2 | Check _join3 | Check |

EndQuery

I get.(fields a, b, c, d in original order)

af bf cf df
__________________________
a a a a
b b b b
c c c c
d d d d

Ie, letters inserted over numbers.

If I work with example elements:
Query
ANSWER: :pRIV:ANSWER.DB

tests\numbers.db | Af | Bf | Cf |
| _x | Check | Check |

tests\letters.db | Af | Df |
| Check _x | Check |

EndQuery


I get the right information in the wrong structure
cf af df bf
_________________________
1 a a 1
2 b b 2
3 c c 3
4 d d 4

What I want is to be able to import fields from one table into the other in the original order.

Needless to say, this is not the table. The original has about 700 entries and perhaps 50 fields.

I know this is easy, but it's the easy ones that get me.







 
Sort by date Sort by votes
Thank you, Tony.

Actually, in the example I need Af and Df from Letters (fields 1 and 4) and the two middle fields from numbers.

The issue is to maintain the original field sequence.

What are the chances of doin this with joins?

I thought:
Query
ANSWER: :pRIV:ANSWER.DB

tests\numbers.db | A | B | C |
Insert | _x | _join1 | _join3 |

tests\letters.db | A | B | C | D |
| Check _x | _join1 | _join3 | Check |

EndQuery



..but as fields on the insert table can't be checked and join only works with insuert (or no?) the final table contains only the fields from letters.
a a
b b
c c
d d
 
Upvote 0 Downvote
These are not the tables, but a practice lab, if you will.

In the actual tables I have my main table, from which address, update date and a few other things have been erased due to a botched import of partially incorrect data from the numbskull main office. (I didn't realize what had happened until I had corrected individual records on the updated file, at which point I have this information correct only on File 1, call it Numbers, if you will, and the rest of the data on the backed up file2, call it letters. I need to marry this **** again. (Main office was given a list of 30 items to correct, color coded..15 minutes max cut and paste job..but didn't have time, and now I have some 700 messed up entries. Do I have words for them and the nimrod data entry fluffette who "didn't have time"?? what do you think?)


Now a different question, which may mean that the problem lies elsewhare.

I have been looking at various sights to bone up on what long forgotten and hit Karen Boatwright's nice faq on queries at
I only get results (but the wrong ones) in an insert query. If I do what seems logical (was plan A)
Query
ANSWER: :pRIV:ANSWER.DB

tests\numbers.db | A | B | C | D |
| _x | Check _join1 | Check _join3 | _join4 |

tests\letters.db | A | B | C | D |
| Check _x | _join1 | _join3 | Check _join4 |

EndQuery

I get nada. Nix. Nuttin. Fergeddaboudid.
empty answer table.

My experience tells me that the missing link here is something incredibly simple and stupid. Any idea what? This ought to work, no?

If I check all (which doesn't make sense anyway) I get the following empty answer table
a b c d a1 b1 c1 d1

Thanks a lot.

JLL

(anyway, what doesn't kill you makes you smarter or meaner or both, so there's probably some good in all this. Time to review anyway.)
 
Upvote 0 Downvote
I think you'd better start using real field names.

Are you SURE the data in the first field of each table is correct?

Cause if so, you can just run a simple series of queries and get what you need. A short script rather than a bunch of manual work.

Better that than continue to dink around and waste your time on this (which I am assuming you would prefer not to do).


Tony McGuire
 
Upvote 0 Downvote
Lost me, and yes, I would prefer not to. Thanks a lot. On the other hand, I need to know how to do this, so it's a learning process.


Still, why are the queries with the joins coming up empty? JLL
 
Upvote 0 Downvote
Query with joins empty?

Probably because ALL of the data in EVERY one of those fields isn't EXACTLY the same.

Joins and Examples are EXACTLY the same thing, used this way.

It doesn't matter whether you have _x or _join1; it means the same thing.

What does 'Lost me' mean?


Tony McGuire
 
Upvote 0 Downvote
No, not so. ALL the keyed data in both tables is the same. (Using "letters and numbers") - just a, b, c, d.

What works, if out of sequence, with a typed example produces a blank table with joins. Bizarre.

Lost me: =huh? How would I do this with a script (don't answer if it's too insipid or complex, and thanks for all your responses).
 
Upvote 0 Downvote
check it out!
a a a 1
b b b 4
c c c 2
d d d 3

You are bygoscht a creative genius, but riddle me this:

Why would calc work where join fails? Just an inquiring mind. Thanks. My work is now cut out for me. JLL
 
Upvote 0 Downvote
'calc' puts the field being calced at the end of the table, after normal fields.

So, that query goes to the checked field, and makes it first.

Then it goes back to the other table, since it is in the query image first, and includes its calc fields. Then it comes back to the second table and includes its calc field.

A 'join' is simple an example element; join elements don't cause the field to be included. I 'joined' the two tables in my example by using the _a example element.

Tony McGuire
 
Upvote 0 Downvote
Thanks. That one I get. I will run them both on the larger table when I have a moment. I think the second will be easier.
 
Upvote 0 Downvote
Code: 
check it out!
a    a    a    1
b    b    b    4
c    c    c    2
d    d    d    3

Is this a typo, or did that last column actually come out that way? If so, ERROR based on the sample data you posted.

Also, please tell me [again] why you need field 'a' from a particular table, since the tables are linked on that field and should be exactly the same.

Tony McGuire
 
Upvote 0 Downvote
neither. In messing around with it I somehow re-sorted the numbers table. Here the original
a 1 1 1
b 4 4 4
c 2 2 2
d 3 3 3

Since field sequence and not sort at this point is what I need, it's perfect.

thanks again.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

caitlind
  • Locked
  • Question
Australian fashion bloggers wear it like this, simple and sophisticated
Replies
0
Views
182
caitlind
caitlind
ColumbiaDiver
  • Locked
  • Question
Field on form refuses to update table using simple ObjectPal - what am I missing?
Replies
1
Views
88
Bystander
Bystander
OCCguy
  • Locked
  • Question
Pdox 11-XP Pro Runtime Error 216 w/simple report calc, please try this 2
Replies
6
Views
57
OCCguy
OCCguy
dblarch
  • Locked
  • Question
Search for field in QBE GUI
Replies
0
Views
23
dblarch
dblarch
teamakesmefart
  • Locked
  • Question
Simple date arithmetic 1
Replies
3
Views
55
SonOfTed
SonOfTed

Part and Inventory Search

Sponsor

Back
Top