Reports :: Selecting Fields From Table For Query

Jan 13, 2014

I am looking for a way to generate a list of all fields within a table, have the end-user select which fields he/she wants to include, and then run the query. I am trying to create this within a form for a nice, easy to use GUI.

I am using Access 2010 on Windows. The fields I need them to select from are in one table, however there are many lookup (tblkp) tables related.

View Replies


ADVERTISEMENT

Queries :: Selecting Fields In Query

Jan 24, 2014

I have a query that is used to capture all the fields that can be possibly used.When run & exported to excel the user then simply deletes the columns with the fields that they don't require.We do this so we don't have to keep creating queries over and over again when you just use one that does all but then delete what you don't need afterwards.

Any way of using a form with tick boxes for each of these fields so when they user ticks the fields they require reporting on then only those fields will be returned in the query.

View 1 Replies View Related

Modules & VBA :: Selecting Fields - Change Text To Include Data From A Table

Nov 5, 2014

I have an on click event to mail a report which works. I want to change the text to include data from a table.

I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.

Code is :
Private Sub cmd_mailreport_Click()
Dim office As Object
Set office = Checks.office
DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _
"info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _
True, ""
End Sub

View 1 Replies View Related

Access 2010 Selecting Data From A Table And Auto-filling Fields

Aug 14, 2015

I have staff table which is a lookup field on the phone call table.when the phonecall form opens I want the employee field to auto fill in the employees name from the lookup field according to there security level.so something like this

On Open
Select Case Forms!frmLogin!cboUser.Column(4)
Case X = 4'the employees security level'
Case X = Insert Table_tblPhoneCalls!EmployeeID"4" into Form_frmPhoneCalls!Employee.
End Select

of course this is a syntax error as I do not know the correct code words.

View 1 Replies View Related

Conditional Query For Selecting Different Fields For Each Record

Oct 17, 2007

I have an interesting report query I am working on. Is it possible to have a query that shows different fields for individual records depending on the content of the record? Let me see if I can explain. Let's say that I have a Status field that can be "Complete", "Active", or "Planned". And lets say also in that table I have Field1, Field2, and Field3. For each record, I don't want to include all three fields (Field1, Field2, Field3), but only one of these fields depending on what is in the Status field.

So, it would be something like:

Select Status, (if Status = "Complete", select Field1), (if Status = "Planned", select Field2), (if Status = "Active", select field3) FROM table;

So, the query would result in 2 fields for each record: the status field, and one of the three other fields depending on what is in the status field for that record.

Thank you kindly for your help.

View 2 Replies View Related

Reports :: Use 1st Query To Populate Fields Then Use 2nd Query To Filter Fields

Apr 30, 2013

Any way in report that I have 2 reference queries just 1 is to populate all details and 2nd query to filter details and will be the final reporting information???

View 5 Replies View Related

Selecting Existing Table Name Using Form's Combobox For A Query

Dec 12, 2006

Folks,
can someone help me on this? I am not sure why the select query inside this procedure is not working. Here is the that routine:

Public Sub temp()
Dim strDocName As String
Dim strTableName As String
Dim strTbl As String
Dim aot As Access.AccessObject
Dim strSQL As String
Dim rpt As Report
Set rpt = CreateReport

strTbl = Forms!frmSearchBoilerGuar!cboTypeOfGuar

For Each aot In CurrentData.AllTables
If aot.Name = "strTbl" Then
strTableName = strTbl
End If

Next aot

strSQL = "SELECT tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, " & _
"strTablename.memGuranItem , strTableName.memLDs FROM tblProjts1 " & _
"FROM tblProjts1 LEFT JOIN strTableName ON" & _
"tblProjts1.intProjectId = strTableName.intProjectId"

rpt.RecordSource = strSQL
strDocName = "rpt"

DoCmd.OpenReport strDocName, acPreview
End Sub


Basically, I am trying to select a table name from the combobox and then use that table name for my query. Then I want to use that query as a recordsource for my report.

Any help is greatly apprecited.

Shan.

View 2 Replies View Related

Reports :: Get Data Fields From Another Table Based On 2 Dates In First Table

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

Selecting Fields

Dec 17, 2006

I have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.

any help or a point in the right direction would be apreciated.

View 1 Replies View Related

Selecting Fields

Dec 17, 2006

I have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.

any help or a point in the right direction would be apreciated.

View 1 Replies View Related

Selecting A Range Of Fields

Dec 17, 2006

I have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.

any help or a point in the right direction would be apreciated.

View 3 Replies View Related

Reports :: Export Data By Selecting Date Range

Oct 27, 2013

I would like to create a excel file to let the user to input the following column.

Date No. of success No. of failure
==== ============ ==============
" " "
" " "
" " "

I would like to have a function that can allow the user to select the date range and its total no. of success and no. of failure. For example , the user select from 31-03-2001 to 31-12-2012 , then output the report which show the total no. of success and no. of failure in this date range , is there existing tamplate that I can use ? if no , what is the simplier way to do it ?

If the data is very large , I want to create a access db to store it, how to do it ?

View 4 Replies View Related

Generating Reports From A Form After Selecting Dates And Parameters

Sep 11, 2014

The input form of the database have one common table containing data for all parameters for row source. As all data was in one table had one ID. My problem is I am trying to run reports from a form after selecting dates and parameters.

For selecting parameters I created dynamic combo box. For dynamic combo box I needed ID and description of the parameters. So I created separate tables for each parameter with ID and Description column. Which ran me into the problem that when I run report based on main input form which has different row source table and my report form has different row source tables and reports doesn't run when I base my queries on the report form.

View 2 Replies View Related

Select DISTINCT - Selecting Two Fields

Aug 9, 2005

Hi

I am trying to use a query to populate a combo box. works fine as I am using one table. I want to select DISTINCT records which works. However i need the ID of that record in the select statement. Anyone know how to select two things in a select distinct query??

Select DISTINCT tbl_Aplaws.Level2 FROM tbl_Aplaws WHERE tbl_Aplaws.Level1 = 'Business' ORDER BY tbl_Aplaws.Level2;

this works. but i need to select the tbl_Aplaws.AplawID as well. throws errors

View 1 Replies View Related

Selecting The Two Highest Values Among Three Fields

Sep 13, 2006

I'm teaching this term, and have put my grades and roster in Access. Each grading opportunity is a separate field.

Students will have to take 3 exams (Exam1, Exam2, and Exam3); however, their grade is based only on the 2 highest exam grades. There are other grades...FinalPaper, Attendance, Paper1, Paper2... the total of the grades is 600 points.

I can do the other calculations... what I do not know how to do is to select the two highest test values from the 3 Exam fields. I coudl do it manually, but I really prefer to simply enter grades, and then have the final grade calculated.

Thanks.

Gayle Ann

View 3 Replies View Related

Selecting Data From 2 Fields Input Into 1 Field

Jul 23, 2006

I trying to create a query that looks at 2 field in one table and give me the resolves in a new field.

Example

Field 1 Field 2 New field
Hat Cap Cap
Dress Dress
Pants Slacks Slacks
Gloves Gloves
Socks Socks

Can you help?

View 4 Replies View Related

Queries :: Selecting All Date Fields Between Two Dates?

Jul 31, 2013

I'm trying to select a range of relevant dates for an amortization calculation (see my earlier thread on this subject here), but I'm having a bit of trouble making the SQL work.

I have a table called "t_AllMonths" that has only one field: MonthStartDate which contains the first day of the month for a very wide range of months over something like a ten-year period. I'm calculating amort for assets which will be amortized for some subset of those months (defined by the asset's Amort Start Date and Amort End Date). Further complicating matters, the amortization may be suspended during certain "hiatus" periods when the asset it not planned to be in use, and may differ by which business units make use of the asset.

So, I have three tables.

Table: t_Assets
Fields: AssetID (autonumber; primary key), Asset_Name, Asset_Cost

Table: t_AllMonths
Fields: MonthStartDate

Table: t_AmortPeriods
Fields: AmortPeriodID, AssetID, Amt_Period_Num (which I don't expect to use in this), StartDate and EndDate

Right now, I'm just trying to pull the range of dates between the earliest amort start date and the latest amort end date. (Min of StartDate and Max of EndDate, respectively) for a given AssetID.

My sql looks like this:

SELECT t_AllMonths.MonthStartDate,
Min(t_AmortPeriods.StartDate) AS MinOfStartDate, Max(t_AmortPeriods.EndDate) AS MaxOfEndDate,
t_Assets.AssetID
FROM t_AllMonths,
t_Assets INNER JOIN t_AmortPeriods ON t_Assets.AssetID = t_AmortPeriods.AssetID
WHERE ((t_AllMonths.MonthStartDate) Between [MinOfStartDate] and [MaxOfEndDate]);

I keep getting an error message that reads "Run-Time Error 3122: You tried to execute a query that does not include the specified expression MonthStartDate as part of an aggregate function."

View 5 Replies View Related

Forms :: Selecting Fields In A Combobox Where ID Matches

Nov 24, 2013

In my Patients table i have a field called PatientID (AUTONUMBER) as my primary key I hve another problem. Is this possible as i hve been trying for two days now. I hve tried a lot of ways but i cant get it to work right. I have a table called dependents. ID , PatientID,,Dependents,DOB,Age id being the PK AND PatientID as a number I have a table called vitals. with ID,PatientID,PatientName( which is a dropdown of Dependents), hpp,sats etc. (This is a subform in datasheet style in another form)

In my table vitals i have the follwing for PatientName . comboxbox.

SELECT Dependents.Dependents, DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")) AS Age FROM Dependents;

So it selects fine in a dropdown like John 48, smith 36.

The problem i have is that it selcts all the records irrespective of PatientID.

View 14 Replies View Related

Reports :: Create Letter Using Fields In Query

Mar 14, 2014

All, using access 2010. I am working with an existing database that have a letter programmed using fields in a query. I have to add to it but having a trouble. Merge in Word was not used. I can't find any examples. The letter is designed in a report design. Using the fields from a query and filtered by "IIF Statements".

View 3 Replies View Related

Reports :: Summing Calculated Fields On Reports And Tables To Include Cents?

Feb 18, 2014

In my tables i have used calculated fields. one of the fields is to "total expenses." In a report, i need to show the sum of all the "total expenses", the filed populates in the report but the cents are missing. for example if the amount is 6080.40 it shows as 6080. how can i get around this? I have tried changing the decimal point value to 2 at which point the value turns to 6080.00 when it should be 6080.40 (i am a beginner at this i am assuming the answer will probably involve c++ or visual basic's, two concepts i am not familiar with.)

View 2 Replies View Related

Queries :: Join Multiple Fields From One Table To Same Table In A Query

Nov 21, 2014

I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.

Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).

View 2 Replies View Related

Selecting From Table

Jan 17, 2007

heya all- ive got sql max working on my table beacause i changed a field type to number and then sql selects the maxium value- but i now need to select a specific number along with its record from that same table.
This specific number will depend on whats been selected in a drop down box- but when im running the sql its comming up with this error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.


Could this be to do with the field- beacuse i needed to change it to numeric type as i wanted to use sql max, and what way could i get round it???
Heres my sql command that im running to get a value:

Code:sub sqlCmd( )'==SQLSTATMENT=================================set objrs = server.createobject ("ADODB.recordset")dim sqlStatmentsqlStatment = sqlStatment & "SELECT * FROM [Areas] WHERE [Area_Code] = '"& Code &"' "objRs.OPEN sqlStatment,objConn,1,3'==============================================end sub

any suggestions??

View 1 Replies View Related

Selecting The Correct Table

Mar 10, 2006

I have an order table and depending on the the product grp it needs to then be able to select the appropriate size scale table.

e.g.

I order suits, and select a size grade A ( there are 7 size grades ) I then then to enter the size scale for that product. I am not sure whether to create 7 differant tables with the different size grades and link that to the table, if so how do i get it select the correct table.

please help...

H

View 1 Replies View Related

Selecting The Correct Table

Mar 10, 2006

Selecting the correct table

--------------------------------------------------------------------------------

I have an order table and depending on the the product grp it needs to then be able to select the appropriate size scale table.

e.g.

I order suits, and select a size grade A ( there are 7 size grades ) I then then to enter the size scale for that product. I am not sure whether to create 7 differant tables with the different size grades and link that to the table, if so how do i get it select the correct table.

please help...

H

View 2 Replies View Related

Selecting A Field Twice From The Same Table ?

Feb 5, 2007

I have a database spread accross a number of tables, I'll simplify it a bit here - there's questions, answers, respondents each as seperate tables.

Respondents includes
RID
Name
PostCode

Questions
QID
Question

Answers
AID
QID
RID
Answer

The answer table contains answers to all the different questions.

How can I query this to compare questions ? grouping answers to spot trends in the data

So I'd want to pull out the answers for questions 1 and 2 showing someone that answered question 1 with Yes and question 2 with No.

Any help would be much appreciated, or even a point in the right direction.
I've played with Crosstabs, multiple queries and pivot tables - none seem to be able to do this.

Thanks :)

View 8 Replies View Related

Selecting 2 Table At Same Time / Please Help

Oct 23, 2004

Hi
I want select 2 table at same time
this is my code and my first table
Code:
strSQL = "SELECT TOP "&Cint(intTopCount)& " * "
strSQL = strSQL & "FROM T_WEBLOG "
strSQL = strSQL & "WHERE b_published = true "
strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"

the above code work very good
but I want select second table

I added this code to above code
Code:
strSQL = strSQL & "SELECT T_IMAGES.* "
strSQL = strSQL & "FROM T_IMAGES "

so this is my code
Code:
strSQL = "SELECT TOP "&Cint(intTopCount)&" * "
strSQL = strSQL & "FROM T_WEBLOG "
strSQL = strSQL & "WHERE b_published = true "
strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"
strSQL = strSQL & "SELECT T_IMAGES.* "
strSQL = strSQL & "FROM T_IMAGES "


but after I added second code to it
I get this error
============
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.

/functions.asp, line 71

===========


whats the problem
Please help me I need it

Thanks

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved