Reports :: Pulling In Data From Another Table
Jun 2, 2015
I have two tables; one has just variances entered, the other is the total number of events for the month (normal events + variances). The variances become the numerator, and the total events is the denominator.The report does counts of variances based on location, type of variance, etc.This is working just fine, but I also need to show on the report the number of variance/total events (%).
I am struggling with pulling the data for total events into the variance report.The variances all have dates, the totals have start and end dates (monthly). For the variances, my query asks for date range, but I want this same date range to be used for pulling in totals from the other table.I am trying to use DSUM("monthlytotal", "tblTOTALS",Between "StartDate=input start date from report query" And "input end date from report query").Should I setup a form that after the start/end dates are enter, the report is launched using these dates?
View Replies
ADVERTISEMENT
Mar 28, 2014
I am using the following control source for a text box on a report: =nz([Raw_Turb], "---")When I do, it pulls data from a different field from the data source. I am pulling the data directly from the data table. I have tried creating a query and get similar results only from a different data field!I have tried the same control source on a different report and it works correctly! Why does it not work on the current report?
View 5 Replies
View Related
Dec 11, 2005
I trying to build a usage tracker. Users will take a reading once a week and enter data. I want to open the form and have 2 fields filled in based off the last reading entered into the table. I have an ID field (autonumber) and I tried using that field -1 to populate my other fields but it does not work. any suggestions
jon
View 1 Replies
View Related
Mar 16, 2012
I have created a database to track the paper files that I am accountable for. Every day I get requests from workers for various files. I have split the database so that multiple users can access the db and edit or change the status of files. In the be I have the main db with fields required for the files and I have another database (table 2) with similar fields that i use to generate an email form that workers fill out to request files. Access then automatically pulls that data from the email form and inserts in to table 2. I pull those requested files then enter the data in to table 1 so that I know the who/what/where of a files location. I want to be able to automatically insert info from table 2 into table one after I have pulled the file and checked it out.
View 2 Replies
View Related
May 17, 2006
Hello,
New to access :eek:
Trying to create a simple data entry form to get information and populate fields from another table. i.e. type in info in one field and the other fields are displayed in the form with information from another table.
Any help apprecated :)
View 3 Replies
View Related
Mar 20, 2015
So I have a text box in a report that I want to pull in data from a field in another report, so the 'control' for my text box looks something like this...
=[Reports]![rptSalesReceiptSub]![Text141] (i.e Text141 is the data I want to pull into my report)
...the path is completely correct but when I run my report I get.#Name? If I run the report that has the data I want, it's fine (in other words Text141 has valid data in it)
View 4 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
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
Jan 23, 2006
Ok, so I am creating an Access Database to track the progress of our collections staff. I need to pull data from a remote SQL database and filter it so that the appropriate records are appended to the appropriate tables in teh Access DB. Any ideas?
View 3 Replies
View Related
Nov 14, 2005
Hi,
Here is my problem. I am trying to use a form to enter data that is contained on one table, have it pull data from another table that is related to the original data, then add data and dump the whole thing off to another table. I would like to do this one item at a time. The only way I can get this to work is to pull all the data at once into the form. Is there any way I can pull just the related data I need by just entering the item number?
Thanks,
D
View 1 Replies
View Related
Nov 22, 2006
I have 2 Tables and 1 form.
Table 1 has:
Product ID #, Product Name , Catalog Number
Table 2 has:
Date, Product Name, Catalog Number
with the Form, You select a Product Name (From table 1) and you can enter a date, which is entered into the Date column in Table 2. The form will also transfer the Product Name to Table 2.
Now, the problem is I can't seem to get the Catalog Number from the Form to enter into Table 2 automatically. It enters the Product Name just fine. I can, in Table 2, click the field and select the Catalog Number from a list, but that's not what I'm trying to accomplish. When a Date is entered into the Form, I'd like it to update Table 2 with the Date, Product Name AND the Catalog Number.. automatically.
is this possible? and if so, how?
I sorry I'm really new to Access, so my terminology is all out of wack. I hope the question is somewhat clear.
View 2 Replies
View Related
Oct 1, 2013
I have a query and search form with this critera in each field for: Employee, Job Number etc.
Example: Like "*" & [forms]![F_SearchFormWide]![Job Number] & "*"
If I put in may the first 3 characters of the job it pulls it up but when I put the full number nothing comes up.
Also the DB has 40,000 records. The total at bottom of query is only 16,000.
I have tried verifying my join connections making sure all jobs are pulling from job table, I tried all 3 ways. Why its not pulling up all my records.
View 5 Replies
View Related
Jul 11, 2013
I have a form and a subform. I want to pull the data (current record) from a field (Unit) from the main form and use it in a report query, which is connected to my subform.
I thought I could create a variable (strVarUnit) then call the variable in query but I'm doing something wrong.
On the main form in the open event I created the variable ...
Dim StrVarUnit as String
On the form after updating the Unit field I placed an event ...
StrUnit = Me.Unit
I then placed Call StrVarUnit() in the query.
This doesn't work (variable undefined).
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
Dec 26, 2014
I have a table/form [tblStdAdv] [frmStdAdv] that contains all advisers a student had during their program. that form pulls from refAdv which has:
Type
1- initial
2- current
3- previous
and then an Adviser field, per record
I need to pull the adviser onto the student's main page [tblStdInfo][frmStdInfo] either "current" (if they have had changes) or "initial" (if they went through without an adviser change) .
I would like to pull the info into the main page so that I only have to change, or add to, the one form able and the main form stays up to date.
i tried using a query and was able to pull either "2" or "1" or "both" but could not make it look for "2" Current first and if not found then use "1" Initial.
View 2 Replies
View Related
Jun 5, 2014
I'm trying to make a button on a form that pulls data from a table in a database on a network share to the current open database (with the form).
Database on network share info:DB location: est.com est estdb.accdb
Table in DB: Table1
The current open database has the exact same table:Table2.In the following VBA line I get Runtime Error 3126: Invalid using of bracketing in name '' (The quotes at the end are 2x ')
Code:
DoCmd.RunSQL "INSERT INTO Table2 SELECT [ est.com est estdb.accdb].Table1.* FROM [ est.com est estdb.accdb].Table1"
I tried everything with changing the [, " and even the path name of the external DB as variant of type string. Nothing seems to work.
View 3 Replies
View Related
Jul 11, 2005
I'm trying to take a database from MS database (Works 7.0) into Access 2002. I've done this before by saving it as a DBase IV file and then opening it as such into access. However, this time I need the EQUATIONS to transfer as well, not just the values. I care little about the form view, or even the values themselves, but need the equations to transfer. Please advise. Thanks. :confused:
Mike S in ohio
View 1 Replies
View Related
Aug 18, 2006
I have several lookup fields in a table that reference data in other tables, each with two fields (unique nummber & text data). The combo boxes in the data entry form work fine, but when I produce reports, the unique number shows up in the output, not the text data from the second field. What can I do to force the data that I need to see in my reports?
View 1 Replies
View Related
Dec 19, 2005
Hey folks.
I have an Access db needing populated via a worksheet with unknown # rows and known # of columns. Before pulling this data in I must do a row count to determine how many records will be added to the db. Otherwise, it would be a very simple thing to just copy the contents of the worksheet over.
Known:
- Only using DAO.
- The access table is already created matching the column count in the worksheet.
- The spreadsheet will have an unknown row count.
- Some rows in the spreadsheet will have various cells empty.
- A completely empty row will mean EOF.
Need:
- I need to determine how many rows are in the spreadsheet before copying into db.
Problem that occurs from attempt:
- Overflow issue. I seem to be counting every single row on the worksheet, even though only 2 contain data.
Any ideas out there? Pasting below my latest attempt.
Dim strStorage as integer
strStorage = CLng(xlsWST.Rows.Count)
Thanks folks.
-Tethys
View 4 Replies
View Related
Dec 13, 2007
I'm attempting to build a simple database for a martial arts tournament registration. The weight division (Fin, Fly, Heavy, etc.) depends on the age, the sex and the weight. I've tried this several different ways, most recently by using one table called "competitors" with all the entrant's information, and several other tables based on the age class/sex (i.e. I have a table called Age 8-9 Male with the weights and divisions in it, and other tables for the other ages/sex).
So, basically, what I'd like to have happen is a report that will lookup in the correct table the division, based on the weight entered. In other words, for a record where the age is 8-9 and the sex is Male, the report compares the weight to the values in the 8-9 Male table, or if the record is a 12-13 Female, it looks in the 12-13 Female table for the weight, and places the correct division in the Division field of the report.
Probably going about this wrong.....but any help would be appreciated.
View 5 Replies
View Related
Apr 13, 2013
I am having a problem pulling some data from a query to populate text boxes in a form
Text27 = DLookup("'SumOfSumOfDocCount'", "SumTotalPerf", "DateReceived=" & Forms.Tracker.Text23.Value & "AND 'BookedInID'=" & Forms.Tracker.BookedInID.Value)
I am trying to pull the sum of document count from the SumTotalPerf query where the datereceived in the query matches the date on the form and the BookedInID in the query matches the BookedInID on the form, at the moment Text27 just displays as blank with no error messages displayed so I am lost as to what im doing wrong, Ive double checked all the spelling for my column names etc and all is correct.
View 8 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
Aug 18, 2015
In my Access Database, for each row, there are two queries I want to pull data from to give me the status of the item in the related columns. In Excel, I use one file with multiple tabs to vlookup the data. How would I accomplish this in Access?
For Example, Jacksonville has a value of Submitted in the Completed Checklist Column and Approved in the Parts List Column. These values currently come from two separate tables. How do I get my database table to update when the status changes for each of the columns?
View 1 Replies
View Related
Dec 26, 2012
I need to pull data from multiple tables in order to show a "financial summary"..Currently I have: Company; BalanceSheet; Debt; Liabilities; Income..All tables have a lot of information (which is why I built them in multiple tables).
I need to build a form where I can use a combo box to select a company from a list.Once selected - I need to the form to pull selected information from each of the above tables. (As well as perform some math functions).I've been struggling with the relationships (They don't seem to make a difference) and I believe I am above and beyond what the wizards will accomplish.
I have read thread after thread but cannot seem to find a specific answer on how to accomplish this.To make matters more complex - Once finished I want to be able to select multiple companies and create a report from the fields mentioned above (IE: pick company A, B, and C and have all of there "current Assets" add up on one report)
This task was originally achieved using an Excel spreadsheet but it has become to confusing for users and difficult to save information for future use.I believe all of my fields are constructed correctly. how to compile the data from multiple tables into one form/report.
View 5 Replies
View Related
Dec 4, 2012
I have a subform that is embedded in a main form. The database itself is a few years old and so I think redesigning is out of the question. What I am wondering is if there is any specific code that can go into the tables themselves and populate the form based on the previous record. I would like for the Transfer to Rehabber fields (To and Permittee) to be duplicated on the Transfer From Rehabber fields (From and Permittee) on the next record. Do you think that there is code that will be able to achieve such a thing?
View 12 Replies
View Related
Oct 29, 2014
Anyway, I would call myself intermediate level at best with Access. I never expected to have to do so much with it, but when my bosses found out I could do Access basics, they began demanding more and more.
I manage an EMR from which a datapull occurs on discharge to various access databases.
They wanted me to add a triage patient data pull to track what procedures are being done to triage patients. So I built a database with the following 2 tables.
tblTriageVisits and tblTriageProcedures
tblTriageVisits has the following fields
-Patient ID
-Export Time
[Code].....
The tblTriageVisits stores all the patients triage visits. The other table stores what was done to each patient on those visits
It should also be noted that this EMR exports times in number of seconds since 12/31/1975, so TimeProcFMS is the number of seconds since that date for example.
The pulls work great and the duplicate record elimination method works great.
I have to design a couple of different reports based on this data and one in particular has pretty much flumoxxed me.
I need to make a report that lists each patient by date they were there and then shows what was done to them. I have tried several different variations of this as both queries and reports. The report breaks down when I try to get it to show just those procedures that would be for that triage visit. I got to the point where it would group by day and then sort the patients alphabetically. Then I added in the visit fields. And it would put all triage procedures for that patient from all their visits in each visit.
So, I was like great, let me filter out those that don't occur between the two dates, admission and discharge
I used the following two formulas (the first one on the procedure name field the second on the procedure date field)
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],[Procedures FMS])
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],DateAdd("s",[TimeProcFMS],#12/31/1975#))
And they work, sort of. They do eliminate those procedures that occur outside of the date range I am looking for. However, the access report still leave spaces in the report where the filtered out items would be.
Did I approach this problem correctly? And if so, how do I get rid of those spaces?
Note, I can read and mostly understand SQL, but am not comfortable working in it yet. And the Access SQL box gives me a headache, it is so jumbled up.
View 14 Replies
View Related
