440,874 Members | 1,027 Online
Need help? Post your question and get tips & solutions from a community of 440,874 IT Pros & Developers. It's quick & easy.

# Creating an access database which checks for missing values

 100+ P: 153 Hey guys I'm a newbie and in fact I'm not even a programmer but decided to take up the task of learning access and creating a database. And I've gotten pretty far in terms of importing from excel and taking serial numbers such as AXT209939300LT and just stripping them down to the number of 209939300 for example. So here's where I am confused...and I've asked this question a couple of times but I've given it more thought and I think this is the easiest way to put it. I have four numbers. They are all rather large numbers such as 10001000. These numbers have other information associated with them but this is not important for this question and the other information can, in no way, be used as a primary key. These numbers all SHOULD have associated equal numbers in the corresponding other three rows but the purpose of this program is to assume that they don't. The program does, however, also assume that there is at least one of each number. The reason for this is because the numbers correspond to computers that the company owns and the four numbers come from four parts of the company that are responsible for keeping track of these computers. If there is not a number then the problem is beyond the control of this program and must be found physically. Since there could be missing values in each row, these values can not be a primary key. Example: Number 1 | Number 2 | Number 3 | Number 4 | 10001000 | 10001002 | 10001002 | 10001003 | 10001001 | 10001001 | 10001003 | 10001000 | 10001002 | 10001003 | 10001001 | 10001001 | 10001003 | 10001000 | 10001000 | 10001002 | but say field Number 1 could be missing 10001003 because that part of the company missed it when they were taking an inventory or it's not connected to the network. This could happen in any of the fields. While I was writing this out I thought perhaps I could create a primary key but taking the values from Number 1 and putting them into a temporary key that I would call something like 'TempPK' and after including values from the first number I would check for each number from Number 2 against the numbers already in TempPK and where there is not a corresponding value in TempPK I would include the value and do this for the length of Number 2 (which I guess I would have to do by creating an autonumber which I would use to find the maximum value of the feild). I would continue this for Number 3 and Number 4. From this I would not have a missing value and I could therefore create a primary key. This seems like it may work but it seems like it will severely slow down the program runtime and put a lot of strain on my computer. Does anyone have any better ideas that would make this run more efficiently? Perhaps some way I can use relationships to complete this? Also does anyone have a more techical way to describe what I'm trying to do here? I've been searching for a long time for an answer on how to do this (but perhaps I haven't been searching in the right way) and once I figure this all out I want to post the code up so other people that run into this will be able to figure this out. Since I am not a programmer by trade it's probably the best contribution I can make considering I will not likely be able to answer other people's questions on this site....Although I am an electronic composer/producer on the side so I guess I could write someone a theme song lol Anyways I digress. Hopefully this didn't take you too long to read. Cheers Kosmös Nov 3 '06 #1
24 Replies

 Expert Mod 15k+ P: 31,566 This is complicated Kosmos, but as you've clearly made an effort to 'put something back', I will give it some thought if I get any time over the weekend. I can't promise anything, but if I can, I'm likely to post some questions here for clarity. PS. I admire your style ;) Nov 3 '06 #2

 Expert Mod 15k+ P: 31,566 Kosmos, Can you describe what a record represents here. Also, why are there four numbers in each record - if an item (in this case PC I think you said) is missing, how can something else take its place. This seems completely illogical but perhaps I'm missing something. Try to give a clear picture of what you're trying to achieve. I'm afraid I've gone through the question in detail and am still confused. What I think I understand, so far, is that it's some sort of tracking system for PC items in a company (or companies - please explain this). I don't have an understanding of why you store the data the way (it seems) you are doing. PS. I hadn't read FreeSkier's response when I typed this up, but it seems he is thinking along similar lines although expressed more precisely than I was prepared to go with my lack of comprehension of the issue. Nov 3 '06 #6

 100+ P: 153 Kosmos, Can you describe what a record represents here. Also, why are there four numbers in each record - if an item (in this case PC I think you said) is missing, how can something else take its place. This seems completely illogical but perhaps I'm missing something. Try to give a clear picture of what you're trying to achieve. I'm afraid I've gone through the question in detail and am still confused. What I think I understand, so far, is that it's some sort of tracking system for PC items in a company (or companies - please explain this). I don't have an understanding of why you store the data the way (it seems) you are doing. PS. I hadn't read FreeSkier's response when I typed this up, but it seems he is thinking along similar lines although expressed more precisely than I was prepared to go with my lack of comprehension of the issue. hey...yeah each computer has only one number, not four. The four numbers in each column represent four different computers...but there should be four of the same numbers in each row (although not in order and numbers can be missing). To clarify...the current report I'm working on for an example to create the program has about 1,200 numbers for each column. Each column has these 1200 numbers in random orders (for example the numbers could be obtained through the network and are in order of IP addresses)...which for my situation makes it random because not every one of the four parts of the company would get these numbers in that way. So I have to compare these four columns of each 1200 rows and match up the numbers that are the same. But I have to account for the ones with missing rows and have them show up in the final table which means I have to go through 1200x1200x1200x1200 possibilities. This could be done by creating a separate number (that we'll call X) that would extract everything from the first number (which we'll call A) then it will go to B and add anything that's not already there to X and then to C...etc... To give a more concrete example lets just say that we have the following Number 1 | Number 2 | Number 3 | Number 4 | 10001000 | 10001002 | 10001002 | 10001003 | 10001001 | 10001001 | 10001003 | 10001000 | 10001002 | 10001003 | 10001001 | 10001001 | 10001003 | 10001000 | 10001000 | --------------| 10001004 | 10001004 | 10001005 | 10001006 | ------------- | 10001005 | 10001006 | 10001004 | 10001006 | 10001006 | 10001004 | 10001007 | 10001007 | 10001007 | 10001007 | 10001005 | ------------- | 10001008 | ------------- | ------------- | I would need this to show up as: 10001000 | 10001000 | 10001000 | 10001000 | 10001001 | 10001001 | 10001001 | 10001001 | 10001002 | 10001002 | 10001002 | --------------| 10001003 | 10001003 | 10001003 | 10001003 | 10001004 | 10001004 | 10001004 | 10001004 | 10001006 | 10001006 | 10001006 | 10001006 | 10001007 | 10001007 | 10001007 | 10001007 | ------------- | 10001005 | 10001005 | 10001005 | ------------- | 10001008 | ------------- | ------------- | I would put the 10001005 at the bottom because it would just be easier than inserting a row and shifting everything else down...and plus I can arrange that after this part of the program is run. Then from there I could easily check, with the null cell query I already wrote, for problems. Hope this is clear. Thanks again. Nov 3 '06 #7

 100+ P: 153 So here's what I was thinking...this is just an idea of how the code would work...I would have fields A, B, C and D that are located in separate tables tblA, tblB, tblC, tblD. (A2, B2, C2, D2 would be the fields in the table which I would be inserting the results from this module) The fields are the four different department numbers which are the same (after I did some work with them...but that's an unrelated part of this program - I basically take the numbers and have some queries that analyze everything in bulk then I export them out to separate tables with different names to eliminate prior relationships created through the rows of the tables which obviously do not match up correctly or I would not have to write this program) --> so from here this is where I've gotten with the code: [PHP] DoCount (A1, tblA) = A DoCount (B1, tblB) = B If A>B then A=c Else B=c Endif x=0 Do until x=(A+1) i=0 Do until i=(B+1) If A.x = B.i - insert B.i into B2.x i=B+1 Elseif i=(B+1) -insert into B2.c c=c+1 else i=i+1 Endif Loop x=x+1 Loop [/PHP] if you're wondering what I would do with the A values I would insert those into this new table prior to doing this. So this is just a quick example of how A would interact with B. Then I would compare C to (A and B) by finding out which field is longer using the DoCount function again. This way, if B is, for example, longer than A, then I could say, compare C to B where B has a value, but where it is null or equal to zero compare C to A ---- and do this whole process until it finds an equal match or until a value (x) reaches the length of the DoCount (and insert extra rows where necessary). Or I could just keep a running number that keeps track of the length as new rows are added...whatever. Anyways let me know what you guys think and if you think this is the best way to do this. I feel that it is the most efficient way and will save the program from going through maybe 25% of the numbers it doesn't have to I guess? Anyways please let me know if you think this is proper code for what I'm trying to do...if it's clear what I'm trying to do lol. Since the idea of how access works is way beyond me...and apparently way beyond the author of the book I read on access...I am somewhat in the dark about the functionality of access which caused me to think, in the beginning I could organize this all with simple relationships between columns that were in the same row on a table but did not correspond to one another in any other way than that. But in my defense, the first sample sheet I was given did have columns that corresponded to one another and the program therefore originally worked lol. Well once again I digress. Thanks for the help and please let me know what you think. -Kosmös Nov 3 '06 #8

 Expert Mod 15k+ P: 31,566 From what you've said in one of your posts, am I right in thinking that, although you've always shown this as a single table with four numbers in each record, it's actually four lists of numbers, each from a different source. To put it another way, four tables with one number field in per table? This is very important. BTW I cannot answer your question or comment on the suitability of your code ATM as I still don't understand what you're trying to do. This is quite a complex issue, and the more complex, the more important it is to communicate the problem clearly and concisely. Nov 3 '06 #9

 P: 76 I think you can most simply do this through a set of queries If you have four tables with a list of computer numbers in each, with some numbers of the set missing from some of the tables you can get what you want in the following way) 1) create a query contrining the full set of numbers. This is done using a uniion query IE Expand|Select|Wrap|Line Numbers   Select Distinct NumberField From ( Select Distinct Numberfield from Table1 Union Select Distinct Number field from Table2 union TSelect Distinct Number field from Table3 Union Select Distinct Number field from Table4) order by NumberField     Call this Query NumberSet Now create a query joining the four tables to the NumberSet Query using left outer joins, IE Expand|Select|Wrap|Line Numbers   Select Table1.NumberField as N1,Table2.NumberField as N2,Table3.NumberField as N3,Table4.NumberField as N4 from  NumberSet left join Table1.on NumberSet.NumberField  = Table1.Numberfield left join Table2.on NumberSet.NumberField  = Table2.Numberfield left join Table3.on NumberSet.NumberField  = Table3.Numberfield left join Table4.on NumberSet.NumberField  = Table4.Numberfield     This second query will get you what you want as it will return a row for every number but those tables where the number is missing will have null values in the corresponding field. IE the putput will look like N1 N2 N3 N4 100003 100003 1000003 100004 100004 1000004 100005 1000005 1000005 1000005 100006 10000006 etc Nov 4 '06 #10

 100+ P: 153 From what you've said in one of your posts, am I right in thinking that, although you've always shown this as a single table with four numbers in each record, it's actually four lists of numbers, each from a different source. To put it another way, four tables with one number field in per table? This is very important. BTW I cannot answer your question or comment on the suitability of your code ATM as I still don't understand what you're trying to do. This is quite a complex issue, and the more complex, the more important it is to communicate the problem clearly and concisely. Yes this is right they are in separate rows...I think the post that Andrew just left me answers my question though but I will see next Friday when I go into work ...I meant to foward the program home but forgot to... but if I have time to create a sample database and check this i'll update sooner Nov 4 '06 #11

 100+ P: 153 I think you can most simply do this through a set of queries If you have four tables with a list of computer numbers in each, with some numbers of the set missing from some of the tables you can get what you want in the following way) 1) create a query contrining the full set of numbers. This is done using a uniion query IE Expand|Select|Wrap|Line Numbers   Select Distinct NumberField From ( Select Distinct Numberfield from Table1 Union Select Distinct Number field from Table2 union TSelect Distinct Number field from Table3 Union Select Distinct Number field from Table4) order by NumberField     Call this Query NumberSet Now create a query joining the four tables to the NumberSet Query using left outer joins, IE Expand|Select|Wrap|Line Numbers   Select Table1.NumberField as N1,Table2.NumberField as N2,Table3.NumberField as N3,Table4.NumberField as N4 from  NumberSet left join Table1.on NumberSet.NumberField  = Table1.Numberfield left join Table2.on NumberSet.NumberField  = Table2.Numberfield left join Table3.on NumberSet.NumberField  = Table3.Numberfield left join Table4.on NumberSet.NumberField  = Table4.Numberfield     This second query will get you what you want as it will return a row for every number but those tables where the number is missing will have null values in the corresponding field. IE the putput will look like N1 N2 N3 N4 100003 100003 1000003 100004 100004 1000004 100005 1000005 1000005 1000005 100006 10000006 etc Hi Andrew, this is exactly what I was looking for. Thanks. I'll try it out when I go back into work Friday and let you know how it goes...but if I have some time this week I'll create a sample database and test it out (I have to write two papers and read two books first). :) Nov 4 '06 #12

 Expert Mod 15k+ P: 31,566 Kosmos, I'll leave you in Andrew's capable hands. I haven't tried the actual code, but I've gone through the logic and it's 'the perfect answer' as far as I can see. In short, if it doesn't work - it's a typo - the logic is good. Nov 4 '06 #13

 100+ P: 153 Kosmos, I'll leave you in Andrew's capable hands. I haven't tried the actual code, but I've gone through the logic and it's 'the perfect answer' as far as I can see. In short, if it doesn't work - it's a typo - the logic is good. Good to hear...thank you also for all of your help Nov 5 '06 #14

 100+ P: 153 Friday morning and so I'm back to work on this project...I've been in the office for a little bit trying to figure this all out so I can put this into a query but I am not exactly clear on how this all works. If anyone does understand this SELECT DISTINCT statement please help I took it to be that I would say for NumberField I would create a seperate table like NumberSet.NumberField which is where I would put the values and then I would say SELECT DISTINCT NumberSet.NumberField FROM (SELECT DISTINCT NumberSet.Numberfield FROM Tbl1.FieldA UNION SELECT etc...) This does not work and I can't seem to find any help online about this command...does this actually mean SELECT DISTINCT or is it just a simple SELECT from a distinct number set and am I getting the rest right, as well? Thanks. I think you can most simply do this through a set of queries If you have four tables with a list of computer numbers in each, with some numbers of the set missing from some of the tables you can get what you want in the following way) 1) create a query contrining the full set of numbers. This is done using a uniion query IE Expand|Select|Wrap|Line Numbers   Select Distinct NumberField From ( Select Distinct Numberfield from Table1 Union Select Distinct Number field from Table2 union TSelect Distinct Number field from Table3 Union Select Distinct Number field from Table4) order by NumberField     Call this Query NumberSet Now create a query joining the four tables to the NumberSet Query using left outer joins, IE Expand|Select|Wrap|Line Numbers   Select Table1.NumberField as N1,Table2.NumberField as N2,Table3.NumberField as N3,Table4.NumberField as N4 from  NumberSet left join Table1.on NumberSet.NumberField  = Table1.Numberfield left join Table2.on NumberSet.NumberField  = Table2.Numberfield left join Table3.on NumberSet.NumberField  = Table3.Numberfield left join Table4.on NumberSet.NumberField  = Table4.Numberfield     This second query will get you what you want as it will return a row for every number but those tables where the number is missing will have null values in the corresponding field. IE the putput will look like N1 N2 N3 N4 100003 100003 1000003 100004 100004 1000004 100005 1000005 1000005 1000005 100006 10000006 etc Nov 10 '06 #15

 100+ P: 153 Hey Neo thanks for the quick reply...this helps a little bit as I did not understand the DISTINCT command but what about after that? I mean if this is an append query I'm familiar with puting an Insert statement before the select. And then do I do SELECT DISTINCT NumberField FROM (SELECT DISTINCT NumberField FROM TblA UNION etc... or do I do from TblA.Field1 ...I have separated the tables into separate rows of numbers but one Field is associated with the Last Name and First Name so I have a number and a Last and a First Name so I need to do a SELECT DISTINCT for that Field Also is there a way I can incorporate that Last Name and First Name into this statement so that its relationship with the first number will stick, but the table organizes based on the four numbers by which I was originally trying to organize by? It seems that perhaps I can do this by the DISTINCTROW option? Nov 10 '06 #17

 100+ P: 153 Actually nevermind about the second part of this question...it doesn't make sense with what I'm trying to do and I guess I could incorporate it a new field after. Nov 10 '06 #18

 100+ P: 153 to further explain my confusion...what does it mean SELECT DISTINCT NumberField FROM - what is this NumberField? Is it a certain type of Command that I use to follow up with the Unions of the Fields from different Tables? or is this where I'm putting the Values? I'm new to this lol so please bear with me...but from what I know when I'm trying to create a set of numbers I would say INSERT INTO blah blah blah and then SELECT is from a field that already exists...but the fields that already exist are included after in the Union statements so I don't understand where this original NumberField is coming from? You dig? lol Ahh I'm so lost Help please :) Nov 10 '06 #19

 100+ P: 153 I have a better grasp of things now...This is what I'm trying to do...It doesn't work: INSERT INTO NumberSet ( NumberField ) SELECT DISTINCT NumberField FROM (SELECT DISTINCT AssetNumber AS NumberField FROM tblAssetNumber UNION SELECT DISTINCT Eracent AS NumberField FROM tblEracent UNION SELECT DISTINCT Getronics1 AS NumberField FROM tblGetronics1 UNION SELECT DISTINCT Getronics2 AS NumberField FROM tblGetronics2) ORDER BY NumberField; When I look at the code again it's been replaced with: INSERT INTO NumberSet ( NumberField ) SELECT DISTINCT NumberField FROM [SELECT DISTINCT AssetNumber AS NumberField FROM tblAssetNumber UNION SELECT DISTINCT Eracent AS NumberField FROM tblEracent UNION SELECT DISTINCT Getronics1 AS NumberField FROM tblGetronics1 UNION SELECT DISTINCT Getronics2 AS NumberField FROM tblGetroni] AS [%\$##@_Alias] ORDER BY NumberField; and I get an error message saying that "SELECT DISTINCT AssetNumber AS NumberField FROM tblAssetNumber UNION SELECT DISTINCT Eracent AS NumberField FROM tblEracent UNION SELECT DISTINCT Getronics1 AS NumberField FROM tblGetronics1 UNION SELECT DISTINCT Getronics2 AS NumberField FROM tblGetroni" can not be found Nov 10 '06 #20

 100+ P: 153 For some reason this works... INSERT INTO NumberSet ( NumberField ) SELECT DISTINCT NumberField FROM [SELECT DISTINCT AssetNumber AS NumberField FROM tblAssetNumber UNION SELECT DISTINCT Eracent AS NumberField FROM tblEracent UNION SELECT DISTINCT Get1 AS NumberField FROM tblGetronics1 UNION SELECT DISTINCT Get2 AS NumberField FROM tblGetronics2]. AS NumberField ORDER BY NumberField; Although I don't understand why I have to put the .As NumberField folowing that when I assigned each Column AS NumberField inside the parentheses Anyways this works like a charm The rest makes sense Thanks Andrew, Neo and FreeSkier Nov 10 '06 #21

 P: 76 Hi Kosmos I have been away a few days so was't able to follow this thread. In my original post the 'numberfield' was merely used as an example field name, it has no specific significance in SQL. You do not need to make the UNION query a make table query as in SQL you can use an existing query in exactly the same way as a table. For example if I have a query called 'Query1' I can create a onther query using this query bas in [code] Nov 10 '06 #22

 P: 76 Hi Kosmos I have been away a few days so was't able to follow this thread. In my original post the 'numberfield' was merely used as an example field name, it has no specific significance in SQL. You do not need to make the UNION query a make table query as in SQL you can use an existing query in exactly the same way as if it was a table. For example if I have a query called 'Query1' I can create a onther query using this query as in [code] Select * from Query1 [code] This means you can take a complex query and break it down into a number of simpler queries and assemble the final result as a query using these simpler queries. This is what my original post did. I had a query to create a complete list of numbers and then used this query in a second query that joined it to the tables from which the list was assembled. The ORIGINAL keyword in the queries was used to ensure the list of numbers did not contain duplicates as this would have resulted in duplicate records in the final query. Nov 10 '06 #23

 Expert Mod 15k+ P: 31,566 Although I don't understand why I have to put the .As NumberField folowing that when I assigned each Column AS NumberField inside the parentheses The syntax [SELECT blah blah blah]. AS Name is used in ACCESS to define a 'subquery'. I believe the standard SQL syntax is actually '(SELECT blah blah blah) AS Name', but for some reason Access treats it differently (until recently, Access couldn't even interpret this form even though it had created it itself). A subquery MUST be renamed ('AS Name' assigns a pseudonym to an item, usually a field, but in this case to the recordset) to be used in the query. Nov 11 '06 #24

 100+ P: 153 ahh okay I see. So I have to assign each small function As NumberField and then the larger function As NumberField as well I guess? Well it works and has been working for quite a while now...although I realized today that my database is growing...this does not make sense to me since I have the program clear the database before and after I import and export from and to excel. When I look at all the tables they are clear. Perhaps there is some temporary database I've created in the background that I need to clear as well? I've posted this question separately in a new discussion. If you happen to come accross this question please post the answer on my newer question. Again thanks for all your help guys. Cheers, Kosmös Dec 1 '06 #25