Union Query & List Box help

[jimmyspinner]

Hi there,

I'm new to Union queries and this forum has been very useful in providing solutions up to now but I've hit a dead end having tried to search through the forum without posting a thread.

Here goes.....

I have built a union query based on two tables to link common fields as below to be in a list box.

SELECT Fame.PledgeDate, 'Fame.Company Name' AS [Company Name], 'Fame.Registered Number' as [Identifier], "Fame" as SourceTable
FROM Fame
where Fame.PledgeDate is not null and 'Fame.Registered Number' is not null
UNION SELECT Portfolio.PledgeDate, portfolio.cust_Name AS [Company Name], portfolio.CustomerID as [Identifier], "Portfolio" as SourceTable
FROM Portfolio
where Portfolio.PledgeDate is not null and portfolio.CustomerID is not null;


I've added the table name as the SourceTable into the list box so that once a double clicks on the record it would go to that record using the Identifier from the list box.

Any ideas????

Cheers

Dave aka jimmyspinner

 

[namliam]

For one thing
'Fame.Registered Number' is not null

That doesnt work... you need to use something like
Fame.[Registered Number]

Beyond that I am not sure what your question is?? You want to know where the record is comming from???
In that case the combo is a 0 based array, which means that
YourComboboxname.columns(0)
Will return the value selected in the first column. upping 0 to 3 will fetch you your table name...

I hope this helps

Greets from Amsterdam and welcome to AWF

 

[jimmyspinner]

Thanks regarding the first point.

The list box is a combination of two tables Fame and Portfolio with different table structures so the link criteria will be the identifier but unless it knows which table to go to I would be in the mire. I think your solution would work and the combo box just references the table I want to open with the link criteria.

I'll give it a go and let you know how it goes

 

[jimmyspinner]

Thanks Mailman, I believe the term is you've delivered!!

 

[namliam]

LOL, I always do deliver... That is why I AM THE ONE, THE ONLY Mailman

Sometimes it takes a little longer... up to a year... but I always do Deliver

Happy to have helped you !

Now a comment:
PLEASE to prevent simular problems, quit using spaces in your column names! Or any names anywhere anytime anyhow anyway...

PLEASE use a naming convention, where all tables and queries can be distinguished from eachiother. Table should be prefixed by tbl, query by qry, etc...

 

[jimmyspinner]

I do in new databases but having started this database years ago when I didn't appreciate the naming conventions I now realise the problems I cause myself are sometimes down to this. I've got 80 stand alone users with this database so changing the existing naming conventions will be some task but I know I'll do this one day if they keep me!

 

[namliam]

LOL OK, yes existing DBs are a nightmare... Just wanted to make sure