How to construct a query that allows a lookup or free text

[Freeflow]

I am an Access newbie. I have had some success in using the query wizard to construct simple queries and have done sufficient reading to be able to use a UNION query to provide one answer to my problem. I'm now just checking if there is an alternative answer which would allow me to consolidate tables. Here is the issue.

I have two tables representing the same information

The fields are id, Version, Product-name, Site

In Table 1 the Version field is obtained from another table by a query lookup

In Table 2 the Version field is entered by the user

The two sets of version numbers are very different formats (may not be relevant as both a text strings)

Table 1 and Table 2 may contain the same Product-name (which IS correct) which is differentiated by the source of the version number and indirectly how that version number is controlled.

I need to include version into a 3rd table in a single column. I have found that I can create create a union query for version for use in the 3rd table.

However, I was wondering if it would be better to combine Table 1 and Table 2 into a single table. To do this I would need to have a query for populating the version field that allowed either the version number to be looked up from another table or added as free text (without that free text affecting the table that is the source of the lookup for some of the version numbers). It took me a while to find out about Union queries so I'm hoping someone can put me out of my misery quicker by letting me know how to have a lookup does what is needed or confirm that it not possible

 

[Freeflow]

If it has occurred to you that you should combine two tables into one table then that's usually a good indication that that's the thing to do.

Yes I can see this. The reason they are two separate tables is that table 1 used to contain more information but I moved the extraneous information to a separate table. To consolidate the two tables into one I need to find a way of allowing the user to add the version number from a lookup or as free text (e.g. both should be available)

 

[jdraw]

Freeflow,

Please tell readers in plain English(business terms) why you need 2 methods of assigning version numbers.
What exactly is a version number? What is it assigned to? Who uses it for what purpose?

Just need some context to understand your situation.

 

[Freeflow]

In Table 1 the version numbers refers to a certificate issued by a Government Agency. The version numbers have an issue date from which can be calculated the expiry date. This allows the user to contact the manufacturer well in advance of expiry to determine if the certificate will be renewed.

In Table 2 the version number is that assigned by the manufacturer. The manufacturer usually notifies the end user well in advance when they are planning changes. there is no expiry data as in the case of Table 1.

These version numbers are related to two of the three different ways in which information on a drug substance can be included in a Marketing Authorisation Application for a Medicinal Product in the EU. Both are generated by agents external to the user company. In the third way, the version number is assigned internally so a different method of control applies and it doesn't need to be tracked in the database.

For table 1 I split out the issue date, version number and some other information to a seperate table. So table 1 has a lookup for the version number and table 2 has a free text entry. So if I combine the two tables into a single list the version field will need to take either a lookup or be a free text entry.

 

[jdraw]

I see these as entities within scope, but you know the details better than any reader.

User
Product
Manufacturer
Certificate
CertificateIssuer

 

[Freeflow]

It's an interesting life isn't it.

Whilst investigating this issue today I tried entering a value not on the lookup query and it was accepted. This value was persistent after closing and reopening the database.

This is the query that provides the lookup I need

SELECT [API-ASMF-List].Version
FROM [API-ASMF-List]
UNION
SELECT [API-CEP-List].Version
FROM [API-CEP-List]
UNION
SELECT [API-32S-List].Version
FROM [API-32S-List]
ORDER BY version;

This is the lookup I use where I get the consolidated version list

SELECT [API-SC-Version Query].[Version] FROM [API-SC-Version Query] ORDER BY [Version];

The ability to use the lookup or freetext seems to be controlled by the 'Limit to List' property for the lookup. If this value is 'no' I can add a free text entry. if the value is 'Yes' then I can't add a free text entry.

It would appear that my problem is solved. However does anyone advise against setting the 'Limit to List' property to 'No'.