Loss of data with Union Query

[Comiccradle]

Good day to you all.

I'm not a pro with Access, although I have made several databases. Usually with reading, trial and error I find my way, but with the database I'm working on now I don't know how to solve the problem. The problem is as follows..... I have two tables one (table PPN) Title en PPN number, and one (table Title) Title and VV number. With the union query I made a new table. With Title, PPN number and VV number. Of the 1470 records 700 have the PPN and 740 do not, although all 1470 title have an PPN. The problem is that the titles can be somewhat different in both tables, this could be an added sub title, or punctuation although they are the same, or meant to be, as an example....

Table Title-----Title: Run and Jump VV: 4875
Table PPN-----Title: Run and Jump excercises PPN: 4879563

Becomes with the union query Run and JumpVV:4875

Another example

Table Title-----Title: Skiprope VV: 5378
Table PPN-----Title: Skiprope PPN: 585898

Becomes with the union query Skiprope VV: 5378 PPN: 585898

Although in the first example the titles are somewhat different they are both the same titles. Do any of you have a suggestion how I can solve the problem that the query will accept the difference in the titles as see them as one.


Many thanks in advance, respectfully CC

[hapm]

I'm not sure if you mean a real UNION query here, I think it is a JOIN query instead. Anyway it would be nice to see the SQL you use for the query. Simply change to sql view and copy the content here.

[ranman256]

I would make an additional field LEFT([Title],instr([[title],":")-1) as "Group", to get the ACUTAL title without any id#s.
You could batch data that way.

[Comiccradle]

I'm not sure if you mean a real UNION query here, I think it is a JOIN query instead. Anyway it would be nice to see the SQL you use for the query. Simply change to sql view and copy the content here.

Thank you for your time, I am unable to get to my computer at the moment, I will place the SQL this coming Monday.

[Comiccradle]

I would make an additional field LEFT([Title],instr([[title],":")-1) as "Group", to get the ACUTAL title without any id#s.
You could batch data that way.

Thank you for your answer, I see, it's like the Excel function =Left(A1;8). Is this usable for all title fields nomatter their length?

[Comiccradle]

I would make an additional field LEFT([Title],instr([[title],":")-1) as "Group", to get the ACUTAL title without any id#s.
You could batch data that way.

A short question, is it not so that if I use this I will get all data from both tables? This would create double titles.

[Comiccradle]

I'm not sure if you mean a real UNION query here, I think it is a JOIN query instead. Anyway it would be nice to see the SQL you use for the query. Simply change to sql view and copy the content here.

SELECT PPN.[ALFABETISCHE LIJST TIJDSCHRIFTEN], PPN.PPN, Volledig1.VV, Volledig1.SFEER, Volledig1.SUBSFEER, Volledig1.OudeTitel, Volledig1.Bewaartermijn, Volledig1.TAAL, Volledig1.Status, Volledig1.DATUM, Volledig1.Actief
FROM PPN LEFT JOIN Volledig1 ON PPN.[ALFABETISCHE LIJST TIJDSCHRIFTEN] = Volledig1.[ALFABETISCHE LIJST TIJDSCHRIFTEN];

I hope you can forgive the Dutch inbetween, however I think you can follow it with the terms used bij Access.

Respectfully, CC.

[Comiccradle]

SELECT PPN.[ALFABETISCHE LIJST TIJDSCHRIFTEN], PPN.PPN, Volledig1.VV, Volledig1.SFEER, Volledig1.SUBSFEER, Volledig1.OudeTitel, Volledig1.Bewaartermijn, Volledig1.TAAL, Volledig1.Status, Volledig1.DATUM, Volledig1.Actief
FROM PPN LEFT JOIN Volledig1 ON PPN.[ALFABETISCHE LIJST TIJDSCHRIFTEN] = Volledig1.[ALFABETISCHE LIJST TIJDSCHRIFTEN];

I hope you can forgive the Dutch inbetween, however I think you can follow it with the terms used bij Access.

Respectfully, CC.

My mistake, it should be the following....

SELECT * FROM Volledig1
UNION ALL SELECT * FROM PPN;

Respectfully, CC

[Comiccradle]

Been reading up on the problem, I think that I need to work with SELECT and DISTINCT UNION. No that's not going to work either, seeing that the titles can differ slightly in the two tables it will not couple all the information from one table to the other. Any help would be greatly appreciated, otherwise I'm looking at alot of cut and paste work.

[ItsMe]

...seeing that the titles can differ slightly in the two tables it will not couple all the information from one table to the other....

If the titles did not vary, you could parse the title using the Left() function as previously mentioned. In order to parse, you need to run the parse queries FIRST and represent the results as an alias FIRST. In SQL, the FROM predicate is FIRST. So you would need to create some sub-queries. An easy way to do this is to create separate query objects and use the separate objects as sub-queries.

Since the titles do vary, the following will not produce consistent results.

Query1

SELECT Left([PPN].[ALFABETISCHE LIJST TIJDSCHRIFTEN],InStr([PPN].[ALFABETISCHE LIJST TIJDSCHRIFTEN],"PPN:")-1) AS Alias_PPN_Title, PPN.PPN

FROM PPN;

Query2

SELECT Left([Volledig1].[ALFABETISCHE LIJST TIJDSCHRIFTEN],InStr([Volledig1].[ALFABETISCHE LIJST TIJDSCHRIFTEN],"VV:")-1) AS Alias_VV_Title, Volledig1.VV, Volledig1.SFEER, Volledig1.SUBSFEER, Volledig1.OudeTitel, Volledig1.Bewaartermijn, Volledig1.TAAL, Volledig1.Status, Volledig1.DATUM, Volledig1.Actief

FROM Volledig1;

Query3

SELECT Query1.Alias_PPN_Title, Query1.PPN, Query2.Alias_VV_Title, Query2.VV, Query2.SFEER, Query2.SUBSFEER, Query2.OudeTitel, Query2.Bewaartermijn, Query2.TAAL, Query2.Status, Query2.DATUM, Query2.Actief

FROM Query1 INNER JOIN Query2 ON Query1.Alias_PPN_Title = Query2.Alias_VV_Title;

[Comiccradle]

If the titles did not vary, you could parse the title using the Left() function as previously mentioned. In order to parse, you need to run the parse queries FIRST and represent the results as an alias FIRST. In SQL, the FROM predicate is FIRST. So you would need to create some sub-queries. An easy way to do this is to create separate query objects and use the separate objects as sub-queries.

Since the titles do vary, the following will not produce consistent results.

Query1

SELECT Left([PPN].[ALFABETISCHE LIJST TIJDSCHRIFTEN],InStr([PPN].[ALFABETISCHE LIJST TIJDSCHRIFTEN],"PPN:")-1) AS Alias_PPN_Title, PPN.PPN

FROM PPN;

Query2

SELECT Left([Volledig1].[ALFABETISCHE LIJST TIJDSCHRIFTEN],InStr([Volledig1].[ALFABETISCHE LIJST TIJDSCHRIFTEN],"VV:")-1) AS Alias_VV_Title, Volledig1.VV, Volledig1.SFEER, Volledig1.SUBSFEER, Volledig1.OudeTitel, Volledig1.Bewaartermijn, Volledig1.TAAL, Volledig1.Status, Volledig1.DATUM, Volledig1.Actief

FROM Volledig1;

Query3

SELECT Query1.Alias_PPN_Title, Query1.PPN, Query2.Alias_VV_Title, Query2.VV, Query2.SFEER, Query2.SUBSFEER, Query2.OudeTitel, Query2.Bewaartermijn, Query2.TAAL, Query2.Status, Query2.DATUM, Query2.Actief

FROM Query1 INNER JOIN Query2 ON Query1.Alias_PPN_Title = Query2.Alias_VV_Title;

Many thanks for taking the time to help me. On the 3rd query I am getting an error, I have decided to make hard word of it. I've changed the PPN field so that I can look up the title en PPN field in the other table and then just click the proper PPN for the missing title. When I have finished I will export the Table to Excel and import it again in Access. Because Access will not allow me to change the PPN field back to tekst. So that I can use it for other purposes later. I'm not sure what the field is called in english, Lookup & Relationshipfield I think.

Again thank you.

[ItsMe]

Lookup fields in tables are a bad idea because they are not fun to use in queries. The value stored in the lookup field is its index. I believe they start at 1 but it may be 0.

So if the first item displayed in the lookup field is Piano and the second is Guitar, the value for Piano is 1 and the value for Guitar is 2. or maybe 0 and 1 respectively.

Better to use a separate table

ID     Field1
1       Piano
2       Guitar
3       Drums

and then store the primary key/id field from the new table in your original table as a foreign key. The original lookup field would be replaced with a foreign key field of long integer type. Store 1 or 2 or..... in the original table

[Comiccradle]

Lookup fields in tables are a bad idea because they are not fun to use in queries. The value stored in the lookup field is its index. I believe they start at 1 but it may be 0.

So if the first item displayed in the lookup field is Piano and the second is Guitar, the value for Piano is 1 and the value for Guitar is 2. or maybe 0 and 1 respectively.

Better to use a separate table

ID     Field1
1       Piano
2       Guitar
3       Drums

and then store the primary key/id field from the new table in your original table as a foreign key. The original lookup field would be replaced with a foreign key field of long integer type. Store 1 or 2 or..... in the original table

Yes it is a different table that I am using, I just have to scroll down per filed to look up the title in the other table with the PPN. Sometimes it's a little difficult to explain things when using the program in a different language.