This is a bit of an odd request but there is a real reason behind it that I will explain if needed.

Basically I have two tables each with a single field.
Both tables have an unknown number of rows, Unknown because they will change.
Table 2 will always have more rows than table 1
I want to merge these two tables into a single table.

I must have all records from table 1 and as many records from table 2 to match.

Alternatively all records from table 2 and all records from table 1 plus nulls to make up the difference


Suggestions welcome.

Len B

So create a query based on both tables with the relationship drawn between the two fields. You can change the data returned by changing the nature of the relationship. Click on the link and Access will prompt you.

Unless you want to delete tables 1 and 2, then keep this query. You can use the query anywhere you might want to use a table, and it will automatically update to reflect changes in your tables.

It's easier than you seem to think!

Thanks for the info Neil but there is a bit of a snag.

Table 1 consists of say 50 rows. Each row is a single field, text 200 characters.

Table 2 has 78 rows, each row is a single field text 2 chars

There is no relationship between the two sets of data.

Basically table 2 is a set of codes XA to ZZ. They have absolutely no significance except that they are a two character code and are a reserved set.

Table 1 consists of text info to which I wish to assign a code. This is purely for reference, There is no significance in which code is assigned to which table 1 text.

All I want to do is assign a text code to the text from the reserved set.

Have tried the suggestion you made but since no relationship exists I have had no joy so far.

I know this must sound a bit of a strange request but there is a sound basis, perfectly willing to explain if you wish

Len B

This is strange. You say that you want assign a code, but it doesn't matter which, and I assume that this must be unique.

If you add a second field to each table and make it an autonumber, this will give you a unique reference for each record. Then use these two autonumbers to join the two tables. This will assign the two letter codes to the text records, uniquely, but in no particular order, other than the order in which they first appeared.

I don't think I want to know why you need this!

Thanks again Neil
Understand exactly what you are saying. let me explain a bit more.
FMECA's are Failure Mode, Effects and Criticality Analysis and as part of the process the Reliability Engineer starts at the component level of an assembly and considers the potential for failure of each component. The Engineer allocates a code starting say AA (Function Failure Code FFC) to each failure mode of each component.

Now when the Engineer starts to look at the next level up in the assembly the FFC getes promoted to a Failure Mode Indicator (FMI). and so on up the structure level. Failures at this level get a FFC that is again promoted to FMI at next level.

So every part at every structure level has a FMI code except where the item in question is a single item, i.e it has no components itself like a bolt or screw because there is no lower level for a FFC to be promoted from.

Still with me ?.

So what's the problem.?. Unfortunately all paperwork and explanation associated with the analysis will refer to FMI codes and there will be a bunch of items in every analysis that have no FMI code... so it is necessary to arbitarily assign one. This is done manually at the moment as is everything else.

So having hopefully not confused you too much...

I can assign an autonumber to the tables no probs but because many FMECA analysis's will be carried out when I generate the list of none coded entries the autonumber assigned to the Text table will soon be out or range of the Code Autonumber.

Is it possible to reset the autonumber by code ?.

Event procedure could be

clear Text table of all entries
reset Autonumber field
Append Text entries
run query to assign code based on autonumber match


I am not a Reliability Engineer and the process I have outlined above I have done so in a very simplistic manner so apologies to any Reliability Engineers reading this post. There is considerably more to it than I have outlined.

Enjoy

Len B

I was right when I said I don't think I want to know why you need this!

You tried to make the problem simple, and then confounded the answer by introducing the complex reality!

Do do this elegantly, I think you're going to need to loop through the records where the code is null and assign the reserved codes in a way that emulates the current process.

Because I'm a dirty hacker, and not a programmer, there's another way (but don't tell the clever people on here)

Write a query that extracts all the records with null in the FMI field and append these to a working table created for this purpose with an autonumber. This gives you a freestanding table as originally envisioned. Run an update query to match the autonumber to the reserved code table. Run an update query based on this table and your original table to insert the new FMI codes. Now empty the working table ready for the next run.

Now tell me the next set of complexities that means this won't work.

Sorry about confusing the situation with facts,

With you all the way

No problem.............well ...............except

This would work fine first time but when I run it again would it work or would the autonumber generated get beyond the autonumber in the codes table.

Thinking while typing

If I blow the table away after doing the update and then use a make table query extracting null FMI records I could then add field to the table as autonumber and with a bit of luck and a fair wind the number would start with 1 again

wouldn't it ?


So process is
extract null FMI records with make table query
add autonumber field
run query assigning arbitary FMI code to original table
delete table made with maketable query



Think you may have solved my problem

Off to put head in fridge

Thanks for helping

Be back if it doesn't work of course

Len B

This would work fine first time but when I run it again would it work or would the autonumber generated get beyond the autonumber in the codes table.
If you compact the db after emptying the table the autonumber will reset.

If I blow the table away after doing the update and then use a make table query extracting null FMI records I could then add field to the table as autonumber and with a bit of luck and a fair wind the number would start with 1 again
To hard man, or too much manual fiddling. See above.

So process is
extract null FMI records run append query
run query assigning arbitrary FMI code to records
run query assigning arbitary FMI code to original table
empty table
compact db

Off to put beer in fridge

Hi Neil

Yup cracked it

decided on process

delete table
maketable query of Effect on Part where FMI is null
DDL query to add field autonumber (Note has to be specified as Counter)
select query between new table and table of default FMI's linked by autonumber
Update Null FMI to (select query FMI where Effect on Part=Effect on part)

Done

Thanks for the discussion, Very helpful to the thinking process having somebody bouncy ideas

Len B

Pleased to have helped!