Assigning auto numbers

[jon98548]

I have a numbering question. In my database, I generate a table from a query. Right now, I am using the autonumber property as the unique ID. I would like to concatenate three fields to create my own ID still unique. I want to join a Division ID (ABC) with the year from my date field (yy) and a protocol ID (####). I need to do this because I will be taking measures from duplicate databases. The difference is only in the Division ID. Where I'm having the mind burp is in where to do this concatenation to populate my table. I will query these tables later for my measures.

Maybe I should explain the table more. I will pull together a set of questions from a query and put them into a table. It is at this point I would like to assign an assessment ID to these questions. I can't do it for each question because one assessment might have 1000 questions. On top of that it will happen in at least 15 divisions.

I hope I've explained this enough, if not I'll be glad to expand. Pie in the sky? Thanks for the help.

 

[jon98548]

Thanks, Pat, I'll try those searches.

One thing I did yesterday was make an append query that concatenated the the data and appended that number. Disadvantages? Thanks and I'm off to search.

 

[jon98548]

OK, Pat, that made for some interesting reading. My boss has a few topics that get him going when I press the "button". It seems I just found a button of yours, but I think I'll stay away from it.

Let me back up a bit and explain my situation. I have a database built for assessing a division within our company. We can enter findings, track resolutions, etc. Each division will have a folder on the server with their own copy of the database in it. We did this to use the security features with the server. I have a "main" database in a folder above these where I need to collect information. As I mentioned before, I put together a set of protocols and append them to a table in the db where the assessor and division rep interact with it to resolve findings. The adjustment to my original question is what is the best way to collect this information from each db into the main db. I am using the autonumber property as the pk in the table, so if I just combine records from each db, I could have redundant IDs. I suppose I need a table in the main db that brings the data from each division db, but then everything gets fuzzy. I thought making this new number would solve that, but I'm not going there. So, how should I collect information? Thanks. I hope this is a bit clearer than mud.

 

[jon98548]

You have talked me out of focing another ID, Pat. I didn't want to find out what would happen in splitting them again. (What would happen, by the way?)

Between our posts, I did some exploring with a union query and I was able to pull my assessments together that way. Then I built three queries from that. One counts findings, plans submitted and resolved findings; these are straight counts. The second counts exclusions; separate because the criteria is 'true'. The third counts plans accepted; separate because the criteria is'1'. Now I thought I would just pull these together with another query. Not!

Do you see anything wrong with joining these assessment tables the way I did? And should I pass the rest of this on to the query forum or can I get some ideas here? Anybody feel free to jump in. Thanks a ton!

 

[jon98548]

Pat, thank you very much for your time. You've been a great help. It is quite possible that I will have to use a make table query. I expect each database to have at least 7500 records in it, but it should max at some point because the data will only go back three years. Then when you do that for 15 - 20 db who knows how slow it will grind.

I'm having a bit of trouble with combining queries for measures, each with different criteria. I'm posting that question on the query side though. Take a look in on it. And thanks again for your help.

 

[jon98548]

OK either our system is coming apart at the seams or this union query is already slowing me down. And I'm only testing with 2 tables. Is there another way to bring the data from these tables together or are you suggesting I use the union query for that and and then base a make table query on the union query and then use my table to pull my measures from? Remember these tables are identical; they just come from different databases. Sorry to beat this to death, but it's beating me to death. Thanks, as usual.

 

[jon98548]

You're talking about making a recordset and loop through each of my linked tables to append to the main table? I've been kicking around VBA a little - enough to know I could get into trouble real quick. I'll dig around and see what I can shake up. Thanks.