Queries :: Pulling Name Instead Of Associated ID Number
May 13, 2013
I have three tables the tblStudentInfo (holding the persons main data), the tblEducation (holding the students education records) and the refInstution (holding all university's and college information).I am pulling PersonID, name, etc from the tblStudentInfo, education records from tblEducation.
the problem is that in the query it displays the institution ID instead of the Institution name. How do i get the query/rpt to show the name instead of the ID?
On the form Institution this is pulled from the refInstitution table and the ID InstName etc is a combo box 0; 1.0; etc so the display on the form shows the name of the university instead of the ID number.
View Replies
ADVERTISEMENT
Jul 22, 2013
Table 1:
Account Number
Start Date
End Date
Cost data**
Budget data**
Table 2 (Imported excel file with cost/budget data):
Account Number
Cost data**
Budget data**
Table 1 is the main table that will be viewable in this database. The idea here is that new Account Numbers can be added to Table 1 throughout the year. It then pulls the cost/budget data into Table 1 based on the matching Account Number between table 1 and 2.
So, if the Account Number (Table 1) = Account Number (Table 2) then it pulls the cost/budget data into the cells on that row. I am trying to make this automated since this data is updated weekly and imported into Table 2 from excel.
View 4 Replies
View Related
Aug 8, 2012
I am trying to pull a number from a text box. I listed the expression for the box below. [Current Record] is a number and Count(*) is a number. I couldn't figure out what field gave me the total records so I did a count instead. If I want to pull the value from just the count portion, what expression would do that?
Code:
=[CurrentRecord] & " of " & Count(*)
Note the spaces on either side of "of"
View 6 Replies
View Related
Jan 28, 2015
I have 2 similar numbers in a table with the following parameters:
Double, Fixed, DecPlaces = 2
One number (GLminor) = 0.10
Second Number (GLmaj) = 0.50
When i use the following code to create and pull the numbers from a recordset the (Gmin) is OK and displays as 0.1 but the (Gmaj) has a problem and displays as 0. So when i'm attempting to use it in an equation (OtherNumber) / (Gmaj) i'm getting a divide by zero error.
Code:
sSQL1 = "SELECT * FROM tblRScales WHERE ID = " & RScaleID
Set rs1 = CurrentDb.OpenRecordset(sSQL1, dbOpenDynaset)
rs1.MoveFirst
Gmaj = rs1!GLmaj
Gmin = rs1!GLminor
I've tried using CDec(OtherNumber) / CDec(Gmaj) and still get the same error.
As both numbers have the same properties in the table parameters i'm completely lost as to why one seems OK while the other seems to round down to 0.
View 2 Replies
View Related
Oct 23, 2013
I would like a user to specify the total number of records they need from a query result. The result must be random, therefore cannot filter on any field or data in the table.
On a form, the user enters the total # of records they need and selects a button that executes this:
DoCmd.RunSQL.SELECT DcountRDP.RDP_IND, DcountRDP.GRP_ST_CD, DcountRDP.AUDIT_STATUS, DcountRDP.AUDIT_TYPE, DcountRDP.AUD_ID, DcountRDP.ACURT_IND, DcountRDP.AUD_DT
FROM DcountRDP
WHERE DcountRDP.MaxRecords = [Forms]![Open_Frm]![IL]
This is not working at all.
How can I make it possible for the user to enter a total needed or be prompted to enter a total and then randomly produce the first top # of records that they specify?
View 1 Replies
View Related
May 2, 2014
I have datasets in tables for some price data, which is monthly. Each month there is a list of unique ID codes for the products ("CAP Codes") and then a price for that product ("LM Retail").The table names are simple dates (2010 01, 2010 02 etc) I want to have a query which would create a table which follows the price of each product through the months. All of that would be simple except not all of the products appear every month, so when I set up a simple link between them all it only outputs products which appear every month. Is there a way that it'll keep the products with just some prices - leaving a blank in the table created when there isn't a price that month?
View 3 Replies
View Related
Oct 6, 2014
I am currently trying to pull a query off from a table that needs to have 3 columns side by side, linking to the same ID number. I seem to have managed to do this by making three seperate queries for each column I need, however, when I put it into one query, it is only pulling through the data form the second column (a total of 273 rows), when it should be pulling through the data from the first column (800+ rows).
The SQL looks like thiss:
SELECT [SM_Antennas_DL-1_Q].[Site ID], [SM_Antennas_DL-1_Q].[Antenna Type], [SM_Antennas_DL-1_Q].[Electrical DT], [SM_Antennas_DL-2_Q].[Electrical DT], [SM_Antennas_DL-3_Q].[Electrical DT]
FROM ([SM_Antennas_DL-1_Q] INNER JOIN [SM_Antennas_DL-2_Q] ON [SM_Antennas_DL-1_Q].[Site ID] = [SM_Antennas_DL-2_Q].[Site ID]) INNER JOIN [SM_Antennas_DL-3_Q] ON [SM_Antennas_DL-1_Q].[Site ID] = [SM_Antennas_DL-3_Q].[Site ID];
So the data DL-1, DL-2, DL-3 is from the same database.
View 8 Replies
View Related
Jan 19, 2015
I have a dlookup which gets the empid on login - i know this works as i fire a msgbox with this, this is stored in a global variable.
i have the following code running on 'form_load' to set securities however its just using the first records and not the ones linked to the specified accont - i have a second messagebox fire just before these dlookups just so i know the global is correct
Code:
Private Sub form_load()
DontKick = 0
DoCmd.SetWarnings False
[Code].....
The EmpIDLogin is 1786 (my own) and i have pasted a copy of the table in question (well a portion)
its only using the first row of checks to provide permissions.
View 14 Replies
View Related
Jun 7, 2013
I've got two nested queries. One finds the oldest inspections from an import table, the second compares that query to the main table again and pulls all 'expired' inspections for each Service Order in the first.
These queries are trimmed down for the essential elements of my question. I can post the full SQL if necessary.
queryDoTheseFirst:
Code:
SELECT TOP 18 ImportTemp.[SO ID], ImportTemp.[Inspection Activity],
[Activity Created]+[AddDays] AS [Due Date]
FROM [Priority List] INNER JOIN (ImportTemp INNER JOIN
queryNeededFirst ON (ImportTemp.[SO ID] = queryNeededFirst.[SO ID])
ON [Priority List].Activity = ImportTemp.[Inspection Activity]
[Code] ....
The refinement I would like to make is, rather than having to pull TOP 18 activities in the final query, just pull TOP 12 [SO ID]s and however many activities come along with them (usually 1 or 2, averages out to about 1.5 so 18 is my compromise). In theory an inspector could have two inspections due on every single property, and would only get 9 unique addresses/[SO ID]s. But I can't figure out how to do that when [SO ID] is no longer unique in the second query.
I suppose I could 'number' the rows in the subquery and add a <=12 criteria on that calculated field, but I'm leery of the processing required (that table contains ~14,000 rows, and most methods of numbering seem to want to use DCount).
View 2 Replies
View Related
Sep 29, 2014
I have a file of transaction history from the accounting system. All of the payroll cash payments are coded as ZG. Payroll accruals are coded as ZC. I need a sum of payroll accruals by department that have the same date or later than the last payroll cash payment. How do I write that query?
View 1 Replies
View Related
Apr 25, 2013
I can't get this to work! I am re-using a database to create new reporting. It was a technician utilization database that I am reworking to show weekend days worked. I changed the date format to Long Date to show the day, then started a query. The query is looking for *Saturday* but pulls nothing in the date field that I just changed to Long Date. What am I missing?
View 2 Replies
View Related
Sep 17, 2013
I have a tblCommets that has 2 columns in it. The first is an identifier (1,2,3) and the second column is a comment associated with that identifier. Is it possible to use a query to pull that comment based on a user selection? So, if the user selects 1 then the comment associated with 1 is pulled, but if the user selects 2 then that comment results.
Code : =Select[tblComments].Column(2)
View 1 Replies
View Related
Mar 6, 2014
So I have two tables:
users
primary key = user_id (AutoNumber)
surname (Text)
forename (Text)
...
user_change
primary key = user_change_id (AutoNumber)
user_id (Number) which relates to the user_id from "users" table
change_type (Text)
action_date (Date/Time)
...
In user_change I record any changes made to the users table.
What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.
I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.
This is the data I want to pull from user_change...
The MOST RECENT action_date WHERE change_type is LIKE "*issued*".
However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.
At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...
View 7 Replies
View Related
Feb 19, 2014
Unable to use Between query without pulling dates out of range.I
f I set the Criteria to Between [Enter the start date:] And [Enter the end date:] and input the dates 1/2/2014 & 1/15/2014 i'm pulling data for those dates but for year 2011, 2012, 2013 and 2014 as well.
if I enter in Between #1/2/2014# And #1/15/2014# it gives me just the dates I requested.I need to be able to have a user use the macro and just enter in the dates they need data for.
Also, I am using the criteria on a date ime field that I have set up an expression on - CreateDate: DateValue([TABLE]![date])the date in the table is stored with time and I just need to show the date.
View 3 Replies
View Related
Jul 18, 2013
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...
View 3 Replies
View Related
Feb 6, 2015
791335.12pack. This is the object that is in one of my access fields. I need to extract the 12 and place that in another column called qty.
View 1 Replies
View Related
Aug 4, 2014
I do same thing each month and would like to make it easier/faster: is there a way to run many queries, one after another?
I cannot use macro, cause Run SQL is restricted to 255 characters and my queries SQL codes are longer than that.
Is there a way to run them in a row? Like run query1, wait till its finished, then run 2..3..4.
View 10 Replies
View Related
Dec 1, 2005
Hi,
Im trying to combine 2 fields below into one column.
ID - 001 (This is formatted in table using 000)
Sometext - "BLAH"
In the query I have written this ...
Newfield: [Sometext] & [ID]
But when I preview the query the data displays as "BLAH1" and not "BLAH001"
what should i do to correct this formatting issue?
many thanks!
View 2 Replies
View Related
Apr 3, 2007
I was about to post a question yesterday, but I found a solution myself and would like to share it. When a new column is computed in the query, there is no way to prescribe the data format of the computed column (=field =variable). It is done automatically by access and it can go wrong. I tried to use Format() function but it changes the look but does not change the type of the column. So what you have to do is to wrap your expression in a data convertion function. I used CDBL() as a wrapper for my expression.
I consider it as a bug of Access because in my situation I had a swich() function that took only numeric fields and produced only number but the data type of the column was set to TEXT. I noticed it in the pivot table later on , when I could only use Count() as aggregate function.
Good luck! and take care of data conversion by yourself, do not let it go automatically!
P.S. If anyone knows a better way to prevent such conversion errors, be my guest!!!
By
Maxim Ivashkov
http://www.4suc6.com
View 2 Replies
View Related
Jul 21, 2015
How do I use the Max() function to get only one number. And also what do I put in the parentheses?
View 14 Replies
View Related
Jun 3, 2013
I have a query the result of which provides me a date field in the format 12-Oct-2010.I want that this field data to be converted to corresponding numbers such as 40463.
View 5 Replies
View Related
Nov 14, 2014
How can a reformat a number from 23 to 23.00 in a query. The format functions reformats it correctly but converts the number to text. I need to format the number as 23.00 and maintain the value as a number, not as a text. How can I do this ?
View 3 Replies
View Related
Oct 24, 2014
i have a query with an inputbox for numbers [Enter Number]..if i want the query to return multiple numbers how would i do this? so the user could have 3 5 and 8 to search for, currently the [enter number] allows them to search for one at a time.
View 1 Replies
View Related
Nov 7, 2005
I have a member roster. I have members who have attended. How do I subtracta list of those who have attended from the roster to find those who havent attended. The only way I know those who have attended is by their sign in date.
View 1 Replies
View Related
Dec 28, 2006
I have created several Queries and I would like to have a column that numbered the results. I have looked all over saw a couple of samples but havent had any luck. Anyone have any suggestions. Thanks for your help and have a good New Year.
Just to clarify basically this is what I need. Loan Number is the Primary key.
# Column Borrower Loan Number
1 smith 123456
2 jones 023567
3 jang 102569
JW
View 2 Replies
View Related
Dec 28, 2006
How could I pull in someone when they do not have data?
There is a name table and data table. My goal is that when I run a parameter query if someone does not have data for that time period to have that name pulled in so that eventually a report could be generated stating there is no data for this person for the time period.
View 1 Replies
View Related
