Queries :: QTREE - Link And Grab Information From 2 Tables
Mar 11, 2015
I have 2 tables, and each table having different information and i want to link those 2 and grab information from both tables.
From below tables, i want to map the Qtree and then grab the rest of the information. for e.g. qtree1 suppose the same as qtree1/a
Table 1
Qtree | Mountpoints | Hostname
qtree1 | MP1 | Host1
qtree1 | MP2 | Host1
qtree2 | MP2 | Host2
Table2
Qtree | InstallDate | Owner
qtree1/a | 1-JAn | Vic
qtree2/a | 2-Jan | Gab
Now, How can i map the Qtree since i cant use = as the qtree are different name.
I tried to use LIKE but no luck as well.
btw, in actual... qtree looks like
//amsdc1-s-12345.europe.xxx.com/dhc-amsdc1-s-03015$
/dev/backuppool02
from above....
/dev or //amsdc1-s-12345.europe.xxx.com/ will appear in the other table to map the rest of information.
View Replies
ADVERTISEMENT
Dec 2, 2013
we have a huge report in our company that includes all our customers, all the visits done by our sales reps, all the products they have and if a product was out of stock in the visit or not.
I want to create a querry that exports a table with 3 columns:
1-->customer
2-->date of last visit
3-->average of out of stock products on the LAST visit
I tried to make a pivot table, managed to grab the last visit (setting the date to maximum) but the average of out of stock products were aggregated totally and not for the last day.
how to handle this in MS access 2010?
View 9 Replies
View Related
Jun 4, 2015
In excel we have this option were we can link a spreadsheet into a web information source , is there any similar and easy method in access ?
View 1 Replies
View Related
Aug 8, 2013
In the scheme below, I want to fetch all the info in Table3 for all the Tiers that has the AppID in Table2. There could be more than 1 Tier using the AppID. I don't know if I'm clear though.
Code:
Table1 Table2 Table3
AppID (PK) _ AutoNb (PK) AutoNB
Name Tier (FK) ---_ Name
- AppID (FK) \_ Tier (PK)
Here's my failing attempt...
Code:
SELECT Table3.*
FROM Table3
WHERE Table3.Tier = (SELECT AppID
FROM Table1
WHERE Table1.AppID = 2002);
View 8 Replies
View Related
Mar 18, 2013
I am working on creating a query to base a form on. I've done it several times in this database, but today, for some reason I cannot figure out how to do it.how I did it before but it looks like the SQL statement got wonky:
Code:
SELECT tblEmpInfo.EmpInfoID, tblEmpInfo.EmpIDFK, tblEmpInfo.JobNumberFK, tblEmpInfo.EmpType AS Expr1, tblEmpInfo.CraftCode AS Expr2, tblJobs.JobSite
FROM tblJobs INNER JOIN tblEmpInfo ON tblJobs.JobNum = tblEmpInfo.JobNumberFK;
Basically I have a look up table and all I'm doing is making it so I don't see the Cert ID it just pops up with the description.
View 2 Replies
View Related
Aug 13, 2015
How do you grab a custom Fiscal Year's values based on the system clock's date?
I am building a query where I want to see the number of closed cases based on the current custom fiscal year with the system clocks date. The report that it feeds only cares about the current FY.
I need the System Clock's FY value in this query
Code:
SELECT shortname AS Station, NZ(TotalCount.TotalCases,0) AS [Cases Complete]
FROM StationList LEFT JOIN (SELECT station, count([Open Issues].ID) AS TotalCases FROM [Open Issues] WHERE [Status]="Closed" GROUP BY Station) AS TotalCount ON StationList.shortname =TotalCount.station;
Within the Query Open Issues I have the FY broken up
Code:
fiscalYear: IIf(Month([Issues].[Opened Date])>=10,"FY" & Year(DateAdd("yyyy",1,[Issues].[Opened Date])),"FY" & Year([Issues].[Opened Date]))
View 4 Replies
View Related
May 13, 2013
I have a couple of fields in Access. To explain I will show here the fields I have and their datatype
ID: Autonummer
Amendment: Text
Basis info: Yes/No
Sex: Yes/No
Period: Yes/No
Salary:Yes/No
Shift:Yes/No
Now, in the amendment field, I've inserted a combo box, with 3 options in it. Let's say option A,B and C...The thing I want access to do, is, when option C is selected, I want fields Period, Salary and Shift to be automatically "Yes".But if option A or B is selected, the the user must choose what the other fields are going to be either Yes or No
View 8 Replies
View Related
May 28, 2013
I am splitting a database and have created the Back end already. When I create the front end and link to the tables on the back end... The front end does not link to all the tables in the back end. The list that comes up when creating the linkings does not show all the tables in the back end. What would cause this?
View 1 Replies
View Related
Feb 27, 2008
I know how to Grab the Right and left Characters and grab the mid characters from a field value
ex: Right([fieldName],4)
My question is how to I grab characters based on a space
Say for instance "Fred Smith" or a name field. There are going to be varing lengths of last names...but the format is the same...First Name, Space, Last Name....
QUESTIONS:
How do I search the string, count to the space, then take all characters after the Space?
One step further how would I grab the middle name in this example?
"Fred M Smith"
View 14 Replies
View Related
Oct 4, 2011
Is there documentation/reading out there on how to query fields and take only a certain number of characters from said field?
We are setting up a user database on a 3rd party web application and they have a 15 character username limit so we want to pull the first 3 letters of a persons first name, middle initial, and first 4 letters of a persons last name to use as the username. We have an Access database will all of this information but I am not sure what the correct query is to complete this task.
John P Roberts = JohPRobe
View 1 Replies
View Related
Apr 4, 2014
How do I grab a value from a query and put in into a text box? I need to grab the name that returns.
Private Sub text_badgeRDM_AfterUpdate()
Dim EMP_ID As String
Dim strsql As String
Dim sqlempinfo As New QueryDef
[code]...
View 3 Replies
View Related
Feb 11, 2015
What I have is two fields, one is a simple date and the other is slightly more complex as it holds a date but in a different structure (a dated case number).
The case numbers are for example, 150211551223 the date being the first 6 numbers and equaling to 11/02/2015. so the code will need to grab this data, spin it around and convert it.
The formula will then be:
simpledate - convertedcasenumberdate = days between.
(simpledate will always be the latest date of the two).
View 7 Replies
View Related
Sep 9, 2013
Before I generate a particular report, the user must input a start and end date. If the user mouses to the generate report button, the query fails to grab the second date that was input. If the user tabs off of it, then it works just fine.
I think I could solve it by requerying the form before I generate the report, but that feels like a bulky work around. Is there a better way?
View 2 Replies
View Related
Aug 9, 2007
Hello -
I am new with Access and fear that this might be a very elementary question - apologies. Basically, I would like to create a few different tables that all have the same base information (like the primary key and name of the data), but different additional information. The only thing that I can't seem to figure out is how to be able to add a new entry to one table and have it show it up in the other tables. Is this possible? Thank you!
View 3 Replies
View Related
Jun 15, 2006
Hello, I was needing some help coming up with the code to search the local hard drive and grab a file and then import it into an Access table. I'm trying to do this on a form if possible. If you know of another way, that would work. Any help would be appreciated. Thanks.
View 9 Replies
View Related
Jun 18, 2015
I want to find a way to grab the text from the window that shows the location of the file using vba in Access.
View 3 Replies
View Related
Dec 24, 2005
Hi There
i am trying to link some more tables to my access front end from the sql server back end
when i select 'ODBC Databases' from the link pop up box, the pop up box just closes
any ideas why, or work arounds would be appreciated
Happy Christmas
View 4 Replies
View Related
Jun 20, 2006
Does anyone know how to link tables automatically/VBA?
I'm trying to import files from our DB2 database in which one file is created monthly and instead of manually importing / linking the files manually, I'm looking to have access link to the new file automatically.
The file names are structured as so..
ITM01 (Jan)
ITM02 (Feb)
ITM03 (March)
Thanks
View 5 Replies
View Related
Mar 3, 2007
Hi All
I enter data in a form "WIP" that is based on a table named "WIP" in the form I have a command button. When I activate the command button it takes me into another form "Materials" which is based on a table named "Materials".
The data that I enter into "Materials" is not linked to the data ie Customer details which I have entered into "WIP".
My aim is to be able to report the materials used.
I have tried variations of relationships but am obviously missing something.
Thanks in Advance
Geoffk
View 2 Replies
View Related
Oct 12, 2005
I have a database with software
ID: autonumber
Name: text
Version: text (some say Beta etc)
Possibly 200 pieces of software
I have a database of computers
ID: autonumber
RoomName: text
ComputerName: text
Description: text
I want to link them so a computer can have up to 200 pieces of software in it or just one piece.
I dont want a table with 200 boolean fields although thats how i will end up doing it if i cant any other way.
Chances are each computer will have about 20 - 30 bits of software installed and therefore the table shouldnt need to be huge.
Anyone have any ideas how i can link these and keep the second table small. Surely i can say Computer [2] has software installed [2, 4, 5, 28, 224] but i dont know how to.
Thanks
.matt
View 3 Replies
View Related
Mar 21, 2006
I have a spilt database and both ends sit on a server. The front end has a short cut on the desktop from five machines.
On one machine I can access the backend but it i try and relink the tables, the linked table manager is empty and i cannot and the "always prompt for new location" option is not available so i cannot even do it individually that way.
Any suggestions of why this would be? Is it a security user issue (though i haven't specifically set up any user or workgroup securities)
I have even tried holding down the Shift key and that doesn't work!!
Thanks
View 1 Replies
View Related
Dec 8, 2005
Hi ppl,
Anyone who thinks that they may be able to help me, there is a copy of the database at :
http://members.iinet.net.au/~a.beardsley/database/StaffProLearning.mdb
Here is my problem, as you will see. In the from called frmStaff, I have created 2 lookup tables that work fine in the main form. In the subform under activites though, I have attempted to create a lookup form for Name and Location of activities. It does not appear to be storing the information from previous entries though. My aim is so that when someone goes to enter a new activity name or location, the data will be saved and be in the list for the lookup next time with a different entry, however, it does not appear to be storing the information in the appropriate table. Any ideas on how to fix this?
Regards,
sugar05
View 3 Replies
View Related
Oct 10, 2004
I am creating my first database and I'm a beginner. I have a table called customers and another table that has customer and product information. What I would like to do is when a new record for my product is entered it also enters the customer information on another table. I have repeat customers though, so I only want it to enter it once. Right now, I have to enter the customer info separately. Customer table contains: Customer ID, first, last, Phone. My product table also has this same info. Is this possible? To enter info on a product form and the selected info be copied into another table at the same time only once.
Please help.
Biz
View 5 Replies
View Related
Nov 28, 2007
Hi,
I am getting information on products stored in a query. I want that query to check a couple tables to see if an item is linked through all of them. If it is not listed in the last table (catalog) I want it to be shown.
Code:Catalog tableVolume Prefixprodno Price15 - CE 0218 9.9915 - CE 0722 3.7215 - CF 0218 12.3615 - CF 0091 14.00Source TableSource MediaCM70904 15 - CE
Code:Batches QueryCustomer Number Prefixprodno Source716933 0218 CM70904716933 0408 CM70904
This is the Select Statement I'm using, which gets the 0218 but not the 0408 I want
Code:SELECT (fieldnames......)FROM [Batches] INNER JOIN ([Source] INNER JOIN [Catalog] ON [Source].[Media] = [Catalog].Volume) ON ([Batches].Source = [Source].[Source]) AND ([Batches].Prefixprodno IN ([Catalog].Prefixprodno))WHERE [Batches].[Customer Number]=716933;
If I do NOT IN I get both 0218 and 0408. I just want 0408. I'm sorry if this seems confusing but any help would be amazing at this point =/
View 3 Replies
View Related
Nov 19, 2012
I am brand new to building a database.What I want is a database to store Quote, Job and Invoicing information. We receive quotes first and then they can, but don't always, turn into jobs. We can also receive a job without quoting it. We currently have two spreadsheets. One is for Quotes and the other is for Jobs.
QUOTES INFORMATION
Quote #
Date
Customer Name
Part #
Part Name
Quote Due Date
Qty
Lead Time
Price
Unit
JOBS INFORMATION
Job #
Qty
Quote #
Customer Name
PO#
Part #
Part Name
Est Hours
Start Date
Due Date
Price
As you can see a lot of the information in the Quote spreadsheet is also used in the Job spreadsheet. (Bold represents duplicated items) We currently type the information into each spreadsheet.
Then there are different forms that are filled out for quotes and jobs that contain the information in the spreadsheets.Is there a way that I can have the QUOTE Table automatically populate the JOB Table information?
View 6 Replies
View Related
Aug 8, 2013
I am trying to determine the best method for linking back to a form from Queries.I have a Form that has info filled out for customer service calls that come in. Among which is a case#. From the Log of information stored from these forms queries have been made to organize the info in different ways; all of which have the case#. What I am trying to do is that in looking at a the queries I want them to be able to click on a case number and be transferred to the appropriate form. This way they can update the form as the case progresses.I am trying to improve upon what was created by another.
View 1 Replies
View Related
