Only IDs visible in Excel Import from Access
Closed
PNyiam
Posts
21
Registration date
Tuesday July 17, 2018
Status
Member
Last seen
September 5, 2018
-
Aug 29, 2018 at 10:34 AM
Blocked Profile - Sep 7, 2018 at 08:14 PM
Blocked Profile - Sep 7, 2018 at 08:14 PM
Related:
- Only IDs visible in Excel Import from Access
- Ms access download - Download - Databases
- Import contacts from sim - Guide
- Position formula in excel ✓ - Office Software Forum
- Number to words in excel - Guide
- Access and downloading - Facebook Forum
2 responses
OK, lets say that your tables are structured as so:
tblMachine
ID--------MachineName-------------Location
100-----"Drill Press"-------1
110-----"Lubricator"-------2
tblBuilding
ID---------BuildingName
1----------"Main Building"
2----------"Production1"
And you want to place the following data into a table called tblMaintenance:
ID----------EQ----------------Location
10000---Drill Press-----Main Building
Then you need to create a JOIN in your query, when you pull the data. SO you will JOIN table based on ID's
Joins work as such:
(INNER) JOIN: Returns records that have matching values in both tables.
LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table.
RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table.
So, in this case, your SQL statement would be:
SELECT tblMachine.MachineName, tblBuilding.BuildingName
FROM tblBuilding
INNER JOIN tblMachine ON tblBuilding.[ID] = tblMachine.[Location]
WHERE (((tblBuilding.BuildingName)="Main Building"));
The above sql will produce:
MachineName BuildingName
Drill Press Main Building
I have also set the RELATIONSHIP of Building to Machine in Relationships. See image:
tblMachine
ID--------MachineName-------------Location
100-----"Drill Press"-------1
110-----"Lubricator"-------2
tblBuilding
ID---------BuildingName
1----------"Main Building"
2----------"Production1"
And you want to place the following data into a table called tblMaintenance:
ID----------EQ----------------Location
10000---Drill Press-----Main Building
Then you need to create a JOIN in your query, when you pull the data. SO you will JOIN table based on ID's
Joins work as such:
(INNER) JOIN: Returns records that have matching values in both tables.
LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table.
RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table.
So, in this case, your SQL statement would be:
SELECT tblMachine.MachineName, tblBuilding.BuildingName
FROM tblBuilding
INNER JOIN tblMachine ON tblBuilding.[ID] = tblMachine.[Location]
WHERE (((tblBuilding.BuildingName)="Main Building"));
The above sql will produce:
MachineName BuildingName
Drill Press Main Building
I have also set the RELATIONSHIP of Building to Machine in Relationships. See image:
Ok, so because I have been spending time trying to find storage for a new HCC with 400TB storage, I have been slow. But, its Friday, and its fun time, so I thought I would crack this open.
There are flaws in the table design.
Here is how far I got:
Let us start with "Two word Data Elements". That is not desirable. You need to NOT/STOP use(ing) TWO word ELEMENTS. Use ThisMethod or This_Method. You can even use tblDateRecorded.
Then, you wish to have a machine from assetlist, but there is no other "combined description" listed to key against.
Here is an example of how to link JOIN the tables:
Below are the tables:
And here is the product.....
This Product finds all machines in the "Main Building".
Does that help out? I understand there is no real answer here, but I hope you see how to scale your project the right way. What's next?
There are flaws in the table design.
Here is how far I got:
SELECT PlannedMaintenance.ID as [ID],
Date Recorded.planeedMaintenance as [Date Recorded],
PlannedMaintenance.Recorded By as [Recorded By],
FacilitiesList.Facilities as [Facility],
Let us start with "Two word Data Elements". That is not desirable. You need to NOT/STOP use(ing) TWO word ELEMENTS. Use ThisMethod or This_Method. You can even use tblDateRecorded.
Then, you wish to have a machine from assetlist, but there is no other "combined description" listed to key against.
Here is an example of how to link JOIN the tables:
SELECT tblMachine.MachineName, tblBuilding.BuildingName
FROM tblBuilding
INNER JOIN tblMachine
ON tblBuilding.[ID] = tblMachine.[Location]
WHERE (((tblBuilding.BuildingName)="Main Building"));
Below are the tables:
And here is the product.....
This Product finds all machines in the "Main Building".
Does that help out? I understand there is no real answer here, but I hope you see how to scale your project the right way. What's next?
Aug 30, 2018 at 07:11 AM
SELECT PlannedMaintenance.Facility, FacilitiesList.Facilities
FROM FacilitiesList
INNER JOIN PlannedMaintenance ON FacilitiesList.[ID] = PlannedMaintenance.[Facility]
How do I go about building this into either a query that will join the facilities and assets with the whole of the PlannedMaintenance table, or add this to the VBA code above that imports the access data into excel? Just now I seem to have a number of bit of the puzzle, but don't know how to join them together.
Thanks.
Aug 30, 2018 at 05:32 PM
Aug 31, 2018 at 06:19 AM
I have gone through the relationships and as far as I can tell these have been secured as much as possible.
I managed to get most of the information to show by running the SQL below to form a query, however as you can see from fields 2 and 3 it just dropped the info into the table, where all I really want is for column 4 to show the current info but associated with the correct information.
SELECT *
FROM FacilitiesList
INNER JOIN PlannedMaintenance ON FacilitiesList.[ID] = PlannedMaintenance.[Facility];
This is some of the excel output from my test data, columns D, E and G are showing the IDs rather than the wanted field data but I don't know how to form the SQL JOIN query so that it will provide all of the JOINS.
So basically I need an SQL Statement that will populate a query with all of the PlannedMaintenance table but overwrite (or JOIN etc); PlannedMaintenance.[Facility] with FacilitiesList.[Facilities], PlannedMaintenance.[Machines] with AssetList.[Combined Description] and PlannedMaintenance.[Frequency] with PeriodType.[PeriodType].
I'm not even sure how clear all of that is, but do you think you could help me with the SQL statement? I have looked through numerous tutorials, forums and pages, but they seem to mostly present two fields from two different tables , rather than presenting one whole table with a value adjusted using a join, so am pretty stuck now. Otherwise maybe I am not using the relationships right so if you know a way to force access to prioritise the linked fields when doing the export to excel that would work as well.
Thanks
Aug 31, 2018 at 11:16 AM
Then, how do you wish tables to be linked?
Sep 2, 2018 at 12:35 PM
[ID] - from PlannedMaintenance(Table)
[Date Recorded] - from PlannedMaintenance(Table)
[Recorded By] - from PlannedMaintenance(Table)
[Facility] - derived from FacilitiesList(Table).[Facilities](Field) (The name shows on PlannedMaintenance(Table) but only the relevant ID number shows on excel import, as per the xls table image in previous post)
[Machine] - derived from AssetList(Table).[Combined Description](Field) (The name shows on PlannedMaintenance(Table) but only the relevant ID number (unique key) shows on excel import, as per the xls table image in previous post)
[Frequency] from PlannedMaintenance(Table)
[Period] - derived from PeriodType(Table).[PeriodType](Field) (The Unique ID Key shows on PlannedMaintenance(Table) but would like the period type displayed
[Description] from PlannedMaintenance(Table)
[Completion Date] from PlannedMaintenance(Table)
[Date Worked] from PlannedMaintenance(Table)
[# Employees Worked] from PlannedMaintenance(Table)
[Completed By] from PlannedMaintenance(Table)
Hope that is what you were looking for.
Thanks.