Joining Query adds extra entries

[roystreet]

Hello,
I have 2 tables I'm combining which contain 2 columns each. Both have one common column named ProjectNum. They both have same amount of records (503 records) I am trying to use a query to put them all into one table matching the project number. When I do this, it brings a result of nearly 700 records. So I then go to the sql portion & input disctinct & then it gives a result of 433 records. Please note some project numbers have multiple records with different dates or revisions. For example: ProjectNum SR001 has more than one record (Containing different info)

Any ideas on how I can do this? I did notice that when there were multiple records with the same project number that the query would duplicate these even more. Like one of the project numbers had 5 records associated with it, but the query made like 25 records. It seems it made 5 sets of the same records?

Thanks,
~roystreet

 

[SkipVought]

Hi,

Please post your SQL.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[roystreet]

Hello - Here is my code:

SELECT NumSignature.ProjectNum, [ProjNum+STCNum].STCNumber, NumSignature.STCSignature
FROM NumSignature INNER JOIN [ProjNum+STCNum] ON NumSignature.ProjectNum = [ProjNum+STCNum].ProjectNum;

Thanks for looking.
~roystreet

 

[lameid]

A Join gives you all the matches for what you join on... So if SR01 ProjectNum has 5 records in one table and 2 for the other, you will end up with 10 records for that Projectnum. This is because you get the 5 records from the one table and the 2 matches for each record. Or in your example you must have 5 records in each table.

You are also using an inner join which throws away any records that do not match (the joined field(s) has to be in both tables or it is not included).

All that considered what is it you are trying to accomplish? Are you wanting all the Project numbers from either side? All of them from one side? It seems like you want to match the projects between the two tables but also match by the ordinal occurence of the records (first with first, second with second). Is that it?

 

[roystreet]

There should be an equal amount of items with the same project number. I'm just trying to grab the signature name & the STCNumber (A certificate number) together into one table. I will be doing this with other things also, but this is a good way to get started.

Thanks!

 

[alvechurchdata]

Even if there are equal numbers of items with the same project number you're still going to get more rows of output than you want. If there are two records in each table for project "A" then then you're going to get the following output rows:

First "A" from table1 with the first "A" from table2
First "A" from table1 with the second "A" from table2
Second "A" from table1 with the first "A" from table2
Second "A" from table1 with the second "A" from table2

You've got a one-to-one relationship here so you need to be using the primary key of each table as the link.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[lameid]

A good question to ask is why are there two sources of information for each? If they come from the same table in another system, you should get them exported together.

 

[roystreet]

Hello,
All information is not from the same table. And the information that is in one table...unfortunately, the old system is made via superbase & it is locked up so I can only get a few columns / rows of data also by exporting it to the clip board. Then I have to place that in an access table - That's part of why I need this, so I can match up those partial tables. Unless someone knows how to "break in" to a old (1995) superbase system.

It will not export to excel, but it will give a screen shot, but that will require manually typing it all out. If I try to save it as a disk file, it uses the "sbq" extension.

If anyone has an idea that this could be achieved feel free to enlighten me!

Thanks,
~roystreet

 

[lespaul]

can you not just get the superbase ODBC driver and connect directly to the source data?

http://www.superbase.com/sbdocu/sbodbc/sbodbc.htm

Leslie

Come join me at New Mexico Linux Fest!

 

[roystreet]

Hello,
I am unable because of security reasons to install further software on the machine I use (I can look further into this to see if an exception can be made) Plus, I need to understand how to do this for future needs as well. Although I did look at that site & I appreciate it.

I figured out a way I can export a lot of the data to txt files & then import them. Wheew! I still have to do it in smaller text files and there are several tables in the superbase. I will need to know how to do this correctly though.

I appreciate all the help.
~roystreet

 

[alvechurchdata]

I appreciate your problems being locked into the tables of an old app. I seem to recall that SuperBase used the dBase file format. Try linking the tables into Access as dBase II, III or IV. They're all much the same except for the memo fields.

As for identifying the matching records, Do the SuperBase tables have a primary key or do they rely on something as crude as the record number to align matching records.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[roystreet]

Hello,
I'm sorry for the slow response - Lot's of stuff I've been busy with lately. Anyway...My issue comes into play that I can't actually open the tables. I am working with data that I export to a txt file that is similar to a csv. Then I open up the file in Access or Excel & can pretty much define the columns by the spacing (It doesn't really have delimiters).
It does a pretty good job, although not perfect. See if I could actually open the table directly that would make my life simpler, but that's just not what I have to work with. I'm getting more of a spreadsheet type of export.

I hope this gives some incite.
thanks,
~roystreet

 

[lameid]

We understand you have two exports of information.

The catch is that you want to restore record integrity to these... A solution is to access the database directly and pull the information out. dBase is a native ODBC driver in Access... Nothing to install. Any reason you haven't tried alvechurchdata's last suggestion?

Alternately, you would have to use recordsets to create records with the data... The caveat here is how would you go about matching the records? If you don't know how to do it manually, you can't program it.

 

[roystreet]

Hi,
I have not tried alvechurchdata method because I have not tried that before. I'm not familiar with that method & I thought that he meant to import from the tables (Of which I can't open) via linking the tables in Access. As I said, I don't recall ever tying this method & I thought it meant I must be open to open the tables.

Through the reports that I can export into text, it appears that I'm usually able to match records via a common Project Number

Thanks,
roystreet

 

[lameid]

You have to be able to access the database file or server depending on how the database works. With a Dbase database I believe the extension is DBF. You should be able to link or import from it much the same way you do from Access... Just change the file type when trying to link or import.

Back to matching... The original solution we were looking at matched on project number but you only want one row for each not all posibilities (you want to keep everything on the same record). The question becomes how do you know that the first project number record goes with the first project number record matching in the other table or the second for that matter?

Table 1
1 A
1 B
3 C
5 D

Table 2
5 E
1 F
3 G
1 H


So how do you know if A goes with F or H?

 

[roystreet]

The superbase files appear to be SBD and SB! extensions. I don't see any DB. To address the question, project number appears to be an available field in various tables. I see that via the reporting section asking which field of each table to export to a report.

A little note, when an existing project is amended (In the old system) it keeps the original record. It then creates a new record with the same project number, but with an added amendment date.

That's how they had the super base setup, that's not how my dbase is setup. I will be bringing all of those values into one record.

Thanks,
~roystreet

 

[lameid]

So you have a project number and date that uniquely identifies the record in both tables... Join on both those fields instead of just project number.

 

[roystreet]

Hi lameid, let me further describe. For example 2 tables may have a project number that is common, amended date, reissued date, revised date, etc. The first initial entry will not have the amended, reissued, nor revised date. The next record will have the same project number, but now will have a amended and reissued date.

Would your suggestion work this way? Which date field do you think I should connect with?

(By the way, I greatly appreciate your help )
Thanks,
~roystreet

 

[lameid]

My best advice is to look at the data and figure out how it matches up and use that field for the join / match. My hunch is amended date... maybe it is both so you need all three fields. Maybe they are from seperate tables and you can't directly match on the dates. That is a more complex solution.