Replies

I don’t really understand what you are asking. You might want to include the SQL for your query and give an example of what it is (or is not) doing.

If you don’t have one, you need a separate table that just contains all the codes. You need to create a new query that uses a Left Join to connect this table to your previous query, something like this:

SELECT … FROM [codetable] LEFT JOIN [old query] ON [codetable].code = [old query].code WHERE [codetable].code IN (…)

I don’t know about anyone else, but when you present a convoluted situation like this (one query is a source for another query which in return is a source for yet another query), it would help me if you could explain the logic behind all the queries. That is, what does each query do and what are you expecting out of it.

I didn’t think you could use dbOpenTable and .Seek on a linked table? Try using dbOpenDynaset and .FindFirst instead.

Perfect normalization is seldom achievable. My favorite saying (from a fellow I knew from another forum and who is an Access MVP) is to “normalize until hurts, then denormalize”).

In your situation, perhaps it is not necessary that any name be associated with a membership in the main membership record? It is just a Membership#, start date, maybe a billing address, etc. Then a child table contains any individuals (along with email addresses, maybe phone#, etc. One immediate question you need to ask yourself is “can there be more than 2 individuals on a membership?” What if a child can be listed?

I’m not saying what you’ve done is necessarily wrong. It would be wrong if you didn’t consider alternatives. If you did consider alternatives and concluded that this design worked best given all factors, then that’s OK.

You need a master table of locations, but you don’t need a “Used” field. Not only don’t you need it, it is a BAD idea; you are dependent on this field being updated properly.

You just need a query that basically looks like this:

SELECT locations.locationID FROM locations LEFT JOIN boxes ON locations.locationID=boxes.locationsID WHERE boxes.LocationID Is Null

Of course, I’m guessing at your table names, but the gist of this query is to return every record from your locations table that does NOT have a record in your boxes table.

You need to read-up on database normalization. Your table design would seem to violate one of the basic principles, which results in your having to go through all sorts of manipulates to get what should be a simple result.

That said, this should work:

SELECT name1, email1 FROM yourtablename WHERE name1 is not null
UNION SELECT name2, email2 FROM yourtablename WHERE name2 is not null
ORDER BY name1

But how do you know what is missing? To know something is missing, you must have some idea of what is available. Do you have a master table of locations? If you do, then this is simple, it is just a query using an Outer Join.

If you don’t have such a master table, how could you possibly determine what is missing? In the example you have above, how do you “know” that A1-10 is missing? Is it possible you don’t have an A1-10? Or is there automatically a 01-99 for each prefix? If so, do you have a master list of such prefixes? This would also make things easier.

But if you don’t even have that, then it gets messy. To start, don’t even think queries for a second, how would you look at your list of records and determine a location is missing? You might start like this:
– Sort the records by location.
– Start at the first record, and assuming it is A1-01, you’d expect the next number to be A1-02. If it is, you are OK; if not, you’ve detected a missing number. But how many?

First all all, does the table you are looking at use “location” as the PrimaryKey? Or are you looking at a table that has a “location” field, but can have multiple records with the same location and you are trying to find which locations have no matching records in this table?

Is the field in question defined as a numeric field?

If you want to accept no more than 5 digits, you can use an input mask of “99999”. For exactly 5 digits, use “00000”.

Put an unbound combobox at the top of your form, with the rowsource being your table. If user enters something “Not In List”, you can pop a msgbox.

If you do data entry into those other table via subforms on the form to which you entered your _Job record, you then set the Linking field properties: the Master property as ID and the Child property as JobID.

Yes, you can do that. The subform containing the info from tblShipHandle will be a continuous-form or a datasheet, so you can see everyone associated with that ship. When you set the master/child linking fields in the subform properties, this limits the contents of the subform to only those handles associated with that ship.

>>I haven’t done it yet, but I can put fields in tblShips to hold the Handle of each person associated with the ship.<<

NO! This is the absolute WRONG way to do it! You need to create another table, call it tblShipHandle or something like that. It can have as few as 3 fields: ID (from the Ship), the Handle, and the position. The information would be presented on frmShip as a subform (the parent and child fields are the ID field). Use a combobox to select the person, and a textbox to enter the position (Don't show the ID field).