Queries :: Field Header In Query Based On Other Field Data

Jun 28, 2013

I have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.

The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".

Is this even possible?

View Replies


ADVERTISEMENT

Queries :: Mark Disappear Field Data Based On Field Data Last

Oct 15, 2014

How Mark disappear field data based on field data last.

Example:I have a field type in the name and on behalf of another field No.

In the case of the Type-B data is deleted Number field, which is before the character.

View 1 Replies View Related

Reports :: Hiding A Field In Page Header Based On A Variable

Oct 6, 2013

I have a report where I number pages based on the Store (muliple stores in report but page numbering resets when Store changes) I do this by manipulating the page number field on the report by code in the report sections to reset it and to increment it.

Code:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me!PageNum = 0
End Sub
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
Me!PageNum = Me!PageNum + 1
End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Me!PageNum = 0
End Sub

The report has a Page Header with various fields and labels and that's been fine however I've had a request to hide a field on subsequent pages (i.e. print it on the 1st page of each store then hide it).

So I added the following code to various sections, but can't get it to work correctly. The fields (label and text box) appear on Page 1 for the first store then get hidden but never return even though when I step through the code it is functioning 'correctly'. The GroupHeader code is performed but the fields don't get displayed on the report. I think is isn't re-painting that section but adding code to the Paint event does nothing, and in fact doesn't actually get run, so not sure when that event is triggered.

Code:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me!PageNum = 0
lblMemberNo.Visible = True
MemberNo.Visible = True

[Code] ....

View 3 Replies View Related

Queries :: Use Expression For Field Header

Jul 22, 2014

I'm writing a group by query to transform data and need to use an expression to set a column header such as max(Date([cDate]).

View 2 Replies View Related

Queries :: Find All Field Header Names Containing String

Mar 21, 2013

I need a query to find all the field header names that contain string "PL-" and along with records contain a value with the field name containing "PL-" within a Access DB table.

View 3 Replies View Related

Queries :: Query To Total Based On Value Of A Field?

Jul 31, 2013

the relevant fields look something like this:

TellerNum - Date - Field1 - Field2 - Field2

I need a way for my query to take all rows with the same TellerNum and add Field1, Field2, and Field3 together for those records. So, there may be 20 records with the same TellerNum, and I need Field 1, 2, and 3 to be added together and then totaled for all 20 records to give me 1 grand total. I need this to be done for each TellerNum, so that the results will be a grand total for each TellerNum. So, the results may look something like this:

TellerNum 1: 486
TellerNum 2: 300
TellerNum 3: 240
etc..

View 3 Replies View Related

Queries :: Query Based On Attachment Field?

Jan 24, 2015

I would like to know what criteria is used to return records based on a field with data type attachment.

I am using "IsNull" and "IsEmpty" but neither of these work.

View 2 Replies View Related

Queries :: Rank Fields Of A Query Based On Another Field

Jan 18, 2015

I have the query below that return a table like:

PLOTNR; period,Value, ID, Basal_area/ha, Basal_area/ha, perc_BA_sp

What I want to is to add another field that rank the perc_BA_sp by PLOTNR descending (thus highest perc_BA_sp values rank one etc.)

Code:
SELECT[Q:INV1-Basal_area_plot-spp].PLOTNR,
1 AS period,
[Q:INV1-Basal_area_plot-spp].Value,
[Q:INV1-Basal_area_plot-spp].ID,
[Q:INV1-Basal_area_plot-spp].[Basal_area/ha],
[Q:INV1-Basal_area_plot].[Basal_area/ha],
([Q:INV1-Basal_area_plot-spp]![Basal_area/ha]/[Q:INV1-Basal_area_plot]![Basal_area/ha])*100 AS perc_BA_spFROM[Q:INV1-Basal_area_plot-spp]

[code]....

View 1 Replies View Related

Queries :: Change Query Field Based On A Form

Oct 29, 2013

I need a query's field to change to what is selected in a combo box.

Example,

I select product 1 in the combo box on a continuous form, after the update there is a requery. The form now displays the data from product 1.

I have tried putting [forms]![form1]![combo1] into the field box of the query. But this only makes the form display "product 1" in every row, it does not take any data from the table.

View 3 Replies View Related

Queries :: Changing Query Outcome Based On Field

Oct 18, 2014

I have a query that is based on Property & tenants. It runs a report that gives me all the property listed with the tenants & other info for the property's. Sometimes the tenant moves out & the property is Vacant, so i have a Vacant flag in the property table.

But when i run the query it prints out the last tenant. Can i pick up the vacant flag & change the tenant name to Vacant on the report? As i don't want to delete the tenant from the tenant table.

View 3 Replies View Related

Queries :: Query To Show Field Based On Last Record ID?

Sep 4, 2014

I am looking to get a query to show my list of customers "Grouped By" [CustomerName], that show only the single [CurrentBalance] field for each customer based on the "Last or Highest" [RecordID].

Also, each customer can have up to 4 different [StockType]'s but at least 1 [StockType].So my results would look like this:

[CustomerName] - [StockType] - [CurrentBalance]
Customer#1 StockType#1 5

Customer#1 StockType#2 4

Customer#2 StockType#1 5

Customer#3 StockType#1 5

View 6 Replies View Related

Update A Field Based On Data Entered In Another Field?

Jan 9, 2015

I am wanting to update data in one field which is being pulled in from another table based on an entry in another field in a form

Attached is the database. In the sales form I want to enter a customer ID which will then pull in the customer name from the customer table and put it in the Customer Field in the sales table.

I know I am duplicating the data by having customer name in both tables which is bad database design!

View 3 Replies View Related

Question On Query To Flag A Field Based On Calculated Data

Apr 25, 2005

I have a table with numerous fields including timeIn and timeOut field (these are date/time format). I need to find out how many people were clocked in during each operating hour (based upon their time in and time out). How can I run a query that will allow me to use the times in and out to 'flag' each hour that each person was on duty (a new field for each hour) so that I can run a count on the completed data for every hour?

View 4 Replies View Related

Queries :: Calculated Field To Display Value Of One Field Based On Another

Jul 23, 2015

I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:

10Jul15: Sales
and then adding Criteria like:
EndDate = #10/07/2015#
It doesnt work.

View 3 Replies View Related

Queries :: Query Based On A Form Which Had Field Names And Some Values

Feb 26, 2014

I have a table listing about 20 elements as field names eg FE, CR, NI, TI and so on.

I have built a form which has a combo box listing these elements by selecting "fields" in the property settings of the combo box & next to this combo box i have 2 text box's where the user can input Min & Max values to pass on to the query.

E.g., FE (chosen from the combo box) value between (Text box1) and Text box 2.

I can run the query to give me values between the 2 text box's by using the following formula in the criteria (Between textbox1 and textbox 2).

The issue i have is to be able to select the element from the listbox, input the min & max values identified and be able to pass this to a query so the query can filter based on the field and values passed?

View 3 Replies View Related

Queries :: Showing A Field Based On Another Field

Jul 8, 2014

I was wondering if it is possible to determine weather to show a field based on the values from another field.

Field1, SHOW_Field1, Field2, SHOW_Field2
Field1 and Field are text, SHOW_Field1 and SHOW_Field2 are Boolean values

So if SHOW_Field1 is true, then show Field1 in the query else hide. I was wondering how to do this, I seem to not be able to do this for some reason.

View 3 Replies View Related

Queries :: Form List Based On A Query Returns Original ID Value Not Field Text

Aug 14, 2014

I have a table x where the field value is selected via a combo box in a form that is populated from another table z.

When I look in x, it appears to have correctly stored the text from z, not the ID number.

I then built a Query, qX, which looks in x and grabs the fields I want. That query shows the text correctly in each selected field.

Now when I build a form, frmQx and use a List control, it displays some of the fields as ID values from the original table z, not the text values.

How can I get round this? I've searched and searched for an answer, sigh. Maybe I'm just not quite certain how to phrase the search.

View 5 Replies View Related

New Query Field Based On Existing Field Logic

Oct 8, 2014

I'm trying to create a new field based on two existing fields as part of a select query. Two tables in the query each have a "HOSPITAL" field which is an indicator for "Y" or "N" for each department (in the DEPTLOOKUP table) and for each facility (in the Facility Lookup table). I want to create a field whereby a new indicator is created so that a Y is given for each record only where the facility and department HOSPITAL indicator field is both "Y". I have attached a word document that has a screenshot of the query in design view as well as the access sql code.

View 3 Replies View Related

Queries - If No Data In Certain Field, Don't Query Record

May 1, 2006

Hi!


How do I write this in the criteria box?


Thanks for your help!!!!

View 3 Replies View Related

Queries :: Parse Data In Query And That Becomes A New Field

May 26, 2013

In a query, I have the following data. If a number has more than six digits, I need to parse past the fifth digit and that becomes a new field. If it has five or fewer digits, no record is entered into Column2. For example:

Column1 Column2
12345
54321
123456 6
12345678 678
123
9876541 41
6

What simple code does it take to perform this in a query?

View 2 Replies View Related

Queries :: Exclude Data In A Field From A Query?

Apr 24, 2014

I have data in a field and I want to write a query to return all the values except the ones that have 2014/* in them. So I want to see the blank ones as well as the one with 2013. So I basically want to return B, C, D, E, F, G, H, J, K, L.

I tried the NOT LIKE function but couldn't get it to work.

View 1 Replies View Related

Queries :: PK Results In Query Instead Of Field Data

Nov 23, 2013

Why does my query return the PK results instead of the field data?

Really new at this but all my other queries work properly...

View 6 Replies View Related

Queries :: How To Capture Data In A Field To Be Used In Query

Feb 12, 2015

How do I capture data in a field (one record) to be used in a query.I have a query with [Unit] as a criteria. I want to capture the unit info in a field then have it used in the query.

View 5 Replies View Related

Queries :: Field Based On Value From Another Field

Jul 9, 2014

I can do it using multiple queries but I would love to find a way to do it in a single query..I have formulated a couple sample tables to show what my source data looks like and the result I would like to get. (See below)

I have a table with items and pricing. Each item will have multiple lines showing the various prices with another field determining what the price is related to.I would like to query the table to output a table showing a single item with the various pricings each in a field. To toss another variable into the mix there are multiple sets of data per item based on a dataid field. So the source has , Item,, Type, Price and dataID. I want to show Item, Price1, Price2,Price3, for a specific itemID.

Source Data
itemtypepricedataID
10115.01aaa
10126.75aaa
10139.95aaa
10227.25aaa
102313.25aaa
103119.95aaa
10327.25aaa

[code]...

I would like to filter for only the aaa.how to do it in a single query.

View 11 Replies View Related

Queries :: Query To Select All Data Regarding A Clicked Field

Mar 1, 2015

I have three tables. TableA contains certain order information, such as the username of the employee, type of order, alias number, etc. TableB contains phone numbers, username of the employee, etc. TableC contains supervisor names and alias numbers.I didn't make these tables but that's what I'm working with. I connected TableA to TableB with the username of the employee, and then TableA to TableC using the alias numbers.

I have a report that summarizes how many orders of each type there are. It shows the supervisor, employee username, alias numbers and then each type of order with the amount of orders of each type under the headers.My boss wants to be able to click on the amount of that type of order and have it show him the details for those orders. This is where my problem is.

I was able to use an Inner Join type of query that allows me to filter the data by order type (however, I have to make a new query for each order type since I'm not too experienced on this). The problem is that I cannot find a way to have a query filter the data by order type AND employee username, so that it only displays the orders taken by a certain employee and it only contains one order type at a time, depending on which order type you clicked.

View 14 Replies View Related

Queries :: Update Query To Replace All Data In Field

Apr 8, 2013

Is it possible for an update query to simply replace all the data in a table's field with the results from a query?

For example, I have Table A with the field Years. This table performs various calculations within based on the field Years.

I would like an update query that simply replaces the Years field data in Table A with a new set of years based on a query's search criteria.

When I run the below query, I get an error saying the query must be updateable- but Im not trying to update the query, rather the Table

I've attached a photo ......

View 1 Replies View Related







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