Help With Table Relationships in Access 2

[Herdrich]

I am creating a database of 100+ companies that will be able to gain access to a facility. I have created an excel document that each company will fill out that will work with accesses requirements. I have all of those excel sheets saved in folders on a network drive that are updated periodically. In Access I linked the excel documents so that any changes made in the network drives will be seen in the access database. My issue is that I want to be able to link all of the companies together in access so when I am searching for an employee from one of the companies I can find them using a form I created. I need a little bit of guidance on how I can link them all together into one table or something like that in access. Any help would be much appreciated. If any additinal information is needed just let me know.

~Glenn

 

[Remou]

A union query may suit:

[tt]SELECT This, That, TheOther From Company1
UNION All
SELECT This, That, TheOther From Company2

<...>
SELECT This, That, TheOther From Companyn[/tt]

http://lessthandot.com

 

[Herdrich]

This is what i think im supose to be doing.

SELECT Last Name, First Name, MI, Drivers License Number, Drivers License State, Last Four of SSN, Date Added, Date Expires, Escort, Mission Esential, Company Name, Point of Contacts Name, Points of Contacts Phone Number
FROM Base Access

UNION ALL SELECT Last Name, First Name, MI, Drivers License Number, Drivers License State, Last Four of SSN, Date Added, Date Expires, Escort, Mission Esential, Company Name, Point of Contacts Name, Points of Contacts Phone Number
FROM CAPONES PIZZA BONIFACE

SELECT Last Name, First Name, MI, Drivers License Number, Drivers License State, Last Four of SSN, Date Added, Date Expires, Escort, Mission Esential, Company Name, Point of Contacts Name, Points of Contacts Phone Number
FROM Access List Test;

Everytime i run it i get an error message that says could not find the object 'Last Name'. It looks like im not corectly specifying where to get the information from. Do you know what i am missing?

This is what My access database is looking like so far

Tables

Base Access
Access List Test (Linked Excel Document)
CAPONES PIZZA BONIFACE (Linked Excel Document)

Queries

Qurie 1 (The one with the information above)

Fourms

Base Access (User interface Form)

 

[Remou]

You will need ton add square brackets to names of tables and fields that have spaces:

SELECT [Last Name], [First Name], [MI, Drivers License Number], [Drivers License State], [Last Four of SSN], [Date Added], [Date Expires], [Escort], [Mission Esential], [Company Name], [Point of Contacts Name], [Points of Contacts Phone Number]
FROM [Base Access]

UNION ALL SELECT [Last Name], [First Name], [MI, Drivers License Number], [Drivers License State], [Last Four of SSN], [Date Added], [Date Expires], [Escort], [Mission Esential], [Company Name], [Point of Contacts Name], [Points of Contacts Phone Number]
FROM [CAPONES PIZZA BONIFACE]

And so on.

This is not a good set up. It is best not to have spaces in field names. You may also wish to read

http://www.r937.com/relational.html.

http://lessthandot.com

 

[Herdrich]

 SELECT [Last Name], [First Name], [MI], [Drivers License Number], [Drivers License State], [Last Four of SSN], [Date Added], [Date Expires], [Escort], [Mission Esential], [Company Name], [Point of Contacts Name], [Points of Contacts Phone Number]
FROM [Base Access]

UNION ALL

SELECT [Last Name], [First Name], [MI], [Drivers License Number], [Drivers License State], [Last Four of SSN], [Date Added], [Date Expires], [Escort], [Mission Esential], [Company Name], [Point of Contacts Name], [Points of Contacts Phone Number]
FROM [CAPONES PIZZA BONIFACE]

SELECT [Last Name], [First Name], [MI], [Drivers License Number], [Drivers License State], [Last Four of SSN], [Date Added], [Date Expires], [Escort], [Mission Esential], [Company Name], [Point of Contacts Name], [Points of Contacts Phone Number]
FROM [Access List Test];

Thx above is what i have now but I am getting a syntax error in FROM clause. Any ideas what else may need to be done.

 

[dhookom]

You forgot one of the UNION ALLs.

Duane
Hook'D on Access
MS Access MVP

 

[Herdrich]

That fixed that problem thank you very much i didnt realize that it had to go between every one. I have one more question then i will be able to complete the rest on my own. I have two different excel sheets that i am sending out to companies one with date expires, escort and mission essential and one without. When i run the quire it asks for information from the table that is not suppose to have it. When i take the information out of the SQL code it gives me an error that the numbers of tables in the two selected tables or queries of a union do not match. How can i exclude it from asking for information on blank cells? Or if a person has no middle name or something like that how can i have it run properly without asking for additional information. I hope i am asking this question correctly if you need further information just ask. Thank you for your time.

 

[Herdrich]

Also am i able to do something like an if then statment? Like if escort is null then No or something like that.

 

[dhookom]

You can substitute [COLOR=red yellow],Null,[/color] or [COLOR=red yellow],"",[/color] in place of any field name in the select. If the SELECT is the first of the union query, you will need to use [COLOR=red yellow],Null as Escort,[/color].

You can also replace a field with [COLOR=red yellow],IIf([SomeField] = "SomeValue",Null,[SomeField]),[/color].

Duane
Hook'D on Access
MS Access MVP

 

[Herdrich]

Im not really understanding what you are showing me to do. Is whats below close to correct or can you give me an example using the Escort Feild.

IF([Escort] = "No",Null,[Escort]),

SELECT [Last Name], [First Name], [MI], [Drivers License Number], [Drivers License State], [Last Four of SSN], [Date Added], [Date Expires], [Escort], [Mission Esential], [Company Name], [Point of Contacts Name], [Points of Contacts Phone Number] FROM [Base Access]

UNION ALL SELECT [Last Name], [First Name], [MI], [Drivers License Number], [Drivers License State], [Last Four of SSN], [Date Added], [Date Expires], [Escort], [Mission Esential], [Company Name], [Point of Contacts Name], [Points of Contacts Phone Number] FROM [CAPONES PIZZA BONIFACE]

UNION ALL SELECT [Last Name], [First Name], [MI], [Drivers License Number], [Drivers License State], [Last Four of SSN], [Date Added], [Date Expires], [Escort], [Mission Esential], [Company Name], [Point of Contacts Name], [Points of Contacts Phone Number] FROM [Access List Test];

 

[dhookom]

No, you would need to include the IIf() not IF() in the SELECT part of your SQL replacing one of the field names as necessary.

Duane
Hook'D on Access
MS Access MVP

 

[Herdrich]

 SELECT * FROM [Access List Test]
UNION ALL 
SELECT * FROM [CAPONES PIZZA BONIFACE]
ORDER BY [Last Name];

This is what it looks like i will be going with for now for my SQL code. Im still having a little trouble with the last part of code you were talking about. I did get the errors to go away by going to the excel database that did not have date expired, escort and mission esential and adding them in then hiding them so they wouldent be seen. So it looks like this database is going to work for me thanks for the help.

 

[dhookom]

I don't really understand your business rules but this query will modify the middle select to display Null for Escort if the value of Escort is the string "No".

SELECT [Last Name], [First Name], [MI], [Drivers License Number], [Drivers License State], [Last Four of SSN], [Date Added], [Date Expires], [Escort], [Mission Esential], [Company Name], [Point of Contacts Name], [Points of Contacts Phone Number] FROM [Base Access]

UNION ALL SELECT [Last Name], [First Name], [MI], [Drivers License Number], [Drivers License State], [Last Four of SSN], [Date Added], [Date Expires], IIF([Escort] = "No",Null,[Escort]), [Mission Esential], [Company Name], [Point of Contacts Name], [Points of Contacts Phone Number] FROM [CAPONES PIZZA BONIFACE]

UNION ALL SELECT [Last Name], [First Name], [MI], [Drivers License Number], [Drivers License State], [Last Four of SSN], [Date Added], [Date Expires], [Escort], [Mission Esential], [Company Name], [Point of Contacts Name], [Points of Contacts Phone Number] FROM [Access List Test];

Duane
Hook'D on Access
MS Access MVP

 

[Herdrich]

SELECT [Last Name], [First Name], [MI], [Drivers License Number], [Drivers License State], [Last Four of SSN], [Company Name], [Point of Contacts Name], [Point of Contacts Phone Number], [Date Added], [Date Expires], [Escort], [Mission Esential] FROM [Access List Test]

UNION ALL SELECT [Last Name], [First Name], [MI], [Drivers License Number], [Drivers License State], [Last Four of SSN], [Company Name], [Point of Contacts Name], [Point of Contacts Phone Number], [Date Added], [Date Expires], IIF([Escort],"Null","No"), [Mission Esential] FROM [CAPONES PIZZA BONIFACE]

ORDER BY [Last Name];

I got it working the way i needed the code is above. Thanks for all your help. I thought that was going to be my last question but i have one more. I will have multiple excel sheets linked to this access database but how can i make changes or replace (using the same name) the excel sheets with new data while the access database is running. I keep getting a locked popup every time i try to access an excel sheet when the database is open..

 

[dhookom]

You might want to open a new thread with this new question if you don't get an answer. I haven't experienced issues within Excel when connected to Access tables/queries.

I'm not sure if you have linked Excel to Access or Access to Excel.

Duane
Hook'D on Access
MS Access MVP