Creating an access database which checks for missing values

[Kosmos]

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....Althoug h 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

[NeoPa]

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 ;)

[Kosmos]

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 ;)

Thanks Neo. Much appreciated :)

I will let you know if I do happen to finish it by the end of the day...although I highly doubt that. Originally I was worried about efficiency because I would be running a module that would ask about 5 million questions...but it wouldn't be storing the answers to those questions...it currently would only store about 1,200 results...since I am not so familiar with programming languages I thought that this might be a load on the computer...but although it may be a slight load on the computer after asking around it seems like this won't be too bad and that it's an essential process...and much better than creating a compound primary key which would be really really slow.

...but yeah I think I have the jist of what I need to do, but now I need to learn the language of how to do it...so any help would be appreciated.

Thanks again

-Kosmös

[freeskier]

If I understand you correctly you are trying to track what parts of the company are using computers. if i misread your question i apologize and this is a waste of time for you.

your tables in this case should look like this

tblComputer
PK Computer
1 100001
2 100002

tblDepartment
PK Department
1 Accounting
2 Human Resources

tblComputer_dep artment
PK Computer Department
1 1 1
2 1 2

in the above example both HR and Accounting would be using computer 10001. your PK should be an autonumber incase your computer's number is changed or a mistake was made typing it in. In the above example you could use Computer_PK, Department_PK for the Computer_Depart mentPK but this isn't really necessary.
don't hurt your head coming up with real-life primary keys - - for the most part they are dangerous to use. Also you should read about database normalization.

one source is
http://www.tomjewett.c om/dbdesign/dbdesign.php?pa ge=normalize.ph p&imgsize=mediu m

also, wikipedia has some good tips.

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....Althoug h 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

[Kosmos]

If I understand you correctly you are trying to track what parts of the company are using computers. if i misread your question i apologize and this is a waste of time for you.

your tables in this case should look like this

tblComputer
PK Computer
1 100001
2 100002

tblDepartment
PK Department
1 Accounting
2 Human Resources

tblComputer_dep artment
PK Computer Department
1 1 1
2 1 2

in the above example both HR and Accounting would be using computer 10001. your PK should be an autonumber incase your computer's number is changed or a mistake was made typing it in. In the above example you could use Computer_PK, Department_PK for the Computer_Depart mentPK but this isn't really necessary.
don't hurt your head coming up with real-life primary keys - - for the most part they are dangerous to use. Also you should read about database normalization.

one source is
http://www.tomjewett.c om/dbdesign/dbdesign.php?pa ge=normalize.ph p&imgsize=mediu m

also, wikipedia has some good tips.

Hey freeskier

thanks for your reply but that is not what I am trying to do. There are four parts of our company that track each and every computer we have. For different reasons we have to make sure each of these numbers for each of these computers is in all of the systems. I am currently working on some code right now that I think will solve the problem...hopef ully...I will try to get the idea of it typed up so I can post it up for discussion before I leave work in an hour.

Thank you very much for your reply

[NeoPa]

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.

[Kosmos]

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)...wh ich 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 1200x1200x1200x 1200 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.

[Kosmos]

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...whateve r. 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

[NeoPa]

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.

[Andrew Thackray]

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

 
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

 
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