By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,874 Members | 1,027 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
24 Replies


NeoPa
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

100+
P: 153
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
Nov 3 '06 #3

P: 65
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_department
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_DepartmentPK 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.com/dbdesign/dbdesign.php?page=normalize.php&imgsize=medium

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....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 #4

100+
P: 153
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_department
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_DepartmentPK 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.com/dbdesign/dbdesign.php?page=normalize.php&imgsize=medium

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...hopefully...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
Nov 3 '06 #5

NeoPa
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

NeoPa
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

Andrew Thackray
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
  1.  
  2. 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
  3.  
  4.  
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
  1.  
  2. Select Table1.NumberField as N1,Table2.NumberField as N2,Table3.NumberField as N3,Table4.NumberField as N4 from 
  3. 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
  4.  
  5.  
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
  1.  
  2. 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
  3.  
  4.  
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
  1.  
  2. Select Table1.NumberField as N1,Table2.NumberField as N2,Table3.NumberField as N3,Table4.NumberField as N4 from 
  3. 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
  4.  
  5.  
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

NeoPa
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
  1.  
  2. 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
  3.  
  4.  
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
  1.  
  2. Select Table1.NumberField as N1,Table2.NumberField as N2,Table3.NumberField as N3,Table4.NumberField as N4 from 
  3. 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
  4.  
  5.  
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

NeoPa
Expert Mod 15k+
P: 31,566
This should help you to understand.
Sorry to post such a lot, but it does explain it fully.

[/i]ALL, DISTINCT, DISTINCTROW, TOP Predicates
Specifies records selected with SQL queries.

Syntax
SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
FROM table

A SELECT statement containing these predicates has the following parts:

Part Description
ALL Assumed if you do not include one of the predicates. The Microsoft Jet database engine selects all of the records that meet the conditions in the SQL statement. The following two examples are equivalent and return all records from the Employees table:
SELECT ALL *
FROM Employees
ORDER BY EmployeeID;

SELECT *
FROM Employees
ORDER BY EmployeeID;

DISTINCT Omits records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT statement must be unique. For example, several employees listed in an Employees table may have the same last name. If two records contain Smith in the LastName field, the following SQL statement returns only one record that contains Smith:
SELECT DISTINCT
LastName
FROM Employees;

If you omit DISTINCT, this query returns both Smith records.

If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results.

The output of a query that uses DISTINCT is not updatable and does not reflect subsequent changes made by other users.

DISTINCTROW Omits data based on entire duplicate records, not just duplicate fields. For example, you could create a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order but without any details about those orders:
SELECT DISTINCTROW CompanyName
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY CompanyName;

If you omit DISTINCTROW, this query produces multiple rows for each company that has more than one order.

DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query. DISTINCTROW is ignored if your query includes only one table, or if you output fields from all tables.

TOP n [PERCENT] Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose you want the names of the top 25 students from the class of 1994:
SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;

If you do not include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.

The TOP predicate does not choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.

You can also use the PERCENT reserved word to return a certain percentage of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose that, instead of the top 25 students, you want the bottom 10 percent of the class:

SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage ASC;

The ASC predicate specifies a return of bottom values. The value that follows TOP must be an unsigned Integer.

TOP does not affect whether or not the query is updatable.

table The name of the table from which records are retrieved.
Nov 10 '06 #16

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

Andrew Thackray
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

Andrew Thackray
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

NeoPa
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

Post your reply

Sign in to post your reply or Sign up for a free account.