Queries :: Display Historic Balance Based On Multiple Fields?

Nov 9, 2014

I have a database that contains details of payments returned to a bank account without sufficient information to identify who the money belongs to.

The DB is used by a team of people to retrieve details of an individual entry and when they have figured out who the money should be sent to they can mark that the payment has been "located". We can only send the refund at certain times of the month, so there is another activity "applied" where we have entered details on internal systems for the refund to be processed. Finally, once a month we physically transfer the money out of the bank account and add a "completed" date to all entries that were "applied" before that date.

My manager would like to be able to look back at a certain date to see what the balance was so he can make charts and whatever else in Excel. The balance will be all payments that were not marked as "completed" on that particular date.

I'm having trouble making a query that will give him the balance for each day in the past.

The important tables/fields here are

TBL_MAIN - dateReceived (date) and amountRefunded (currency)

TLK_LOCATED - appliedDate (date/Time) completedDate (date/Time)

TBL_DATES - activityDate - just a list of dates, which I use for a similar query of the amount we located/applied each day

So I'm looking for a query that will list all dates in tbl_dates against the sum of amountReceived up until that date, with the sum of amountReceived where completedDate is less than the date listed subtracted.

View Replies


ADVERTISEMENT

Queries :: Multiple Table Database - Lookup For Some Fields And Display Records

Mar 23, 2015

My colleague has a multiple table database and uses lookups for some fields. The simple query was to extract 3 fields and display all records from those fields. The result of the query was that the selected fields were listed first followed by all other fields which are usually not displayed.

View 5 Replies View Related

Queries :: Sum A Value Based Off Multiple Fields

Oct 22, 2013

I'm trying to build a query that uses the information pulled from controls on a form called "CharacterCreation" - 2 of the controls are "Race" and "Class", and the form stores this data in a "Characters" table.The query needs to refer to the Characters table, and another table called "Modifiers".In this second table I have several numerical fields such as hp, str, etc, and I have two other fields called "Type_Modifier" and "Type" Among the Type_Modifiers are Race and Class, where their corresponding "Type" could be for instance Demon and Magician..

So the idea is that when I mark on the form (and thus creating the record in the first table) a Race of "Demon", and a class of "Magician" I want the query to refer to both of these fields, and sum the values in the second table where the "Type" is either "Demon" or "Magician" or whatever I decided to choose. I feel like I should be able to figure this out but I'm having a hard time..Here's an example of the SQL I've tried that shows nothing.

Code:
SELECT Characters.Char_Name, Sum(Modifiers.hp) AS SumOfhp
FROM Modifiers INNER JOIN Characters ON (Modifiers.Type = Characters.Class) AND (Modifiers.Type = Characters.Color2) AND (Modifiers.Type = Characters.Color1) AND (Modifiers.Type = Characters.Species) AND (Modifiers.Type = Characters.Race)
GROUP BY Characters.Char_Name, Modifiers.Type
HAVING (((Modifiers.Type)=[Characters].[Race]));

I also tried this, which I thought would work but it displays nothing as well.

Code:
SELECT Characters.Char_Name, Sum(Modifiers.hp) AS SumOfhp
FROM Modifiers INNER JOIN Characters ON (Modifiers.Type = Characters.Class) AND (Modifiers.Type = Characters.Color2) AND (Modifiers.Type = Characters.Color1) AND (Modifiers.Type = Characters.Species) AND (Modifiers.Type = Characters.Race)
GROUP BY Characters.Char_Name, [Type]=[Characters].[Race];

View 12 Replies View Related

Display Fields Based On Number

May 14, 2005

Hi Guys and Girls,

While doing a rabbit DB for the missus I have hit a problem, if one of the does gives birth to 3 babies i wish to have the details entered onto a form (not a problem) but i would like to do this:

if number of babies = 3 then fields to display to enter details is 3 on the form, i have my table setup with up to 10 seperate field for the babies per record but am stuck trying to do this

any help or direction would be great

View 8 Replies View Related

Forms :: Display Data Based On Previous Record But Not All Fields

Jan 22, 2015

There is a form which the user fills in 8 fields and saves it. Once saved, a new record opens.

However, I want to modify the save button to save the previous record and display a new record with only a limited no of fields to display from previous record, say 4 fields display from previous record, and the user fills the remaining 4 and saves it. Thus, the save cmd should continue to display 4 fields from the previous record.Already tried below code for save button but it displays all data of previous record as it is, doesn't display blank space for combo.118 and text2

DoCmd.GoToRecord , , acNewRec
DoCmd.GoToRecord , , acPrevious
Combo118.Text = ""
text2.Text=""

View 1 Replies View Related

Table With Multiple Like Fields That Need To Display Into Array-like Format

Jan 8, 2013

I have a table that contains the following;Company name, PositionID, Firstname1, Lastname1, title1, gender1, Firstname2, Lastname2, title2, gender2, Firstname3, Lastname3, title3, gender3 [...up to 50 names]

The data is uploaded to my table from a 3rd party database source via .csv file.I can capture up to 50 names, titles and gender per record. I would like to display this data in a table/array like format. I'm not too good at VBA but I can use expressions for unbound fields.I have attached a file that shows the data in a format I would like to see in Access displayed in a form.

View 2 Replies View Related

Queries :: How To Count Records Based On Multiple Criteria From Multiple Tables

Jan 4, 2014

I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".

View 4 Replies View Related

Queries :: Query / Count And Display Based On Date?

Dec 18, 2014

I am trying to run query on a months worth of dates, have it count based on each day and then display the date and the number?

Table:

field1 - field2 - field 3 - Date

I can run a query one day at a time but would like to run it for the month and get this

12/01/2014 - 15
12/02/2014 - 32
12/03/2014 - 0
12/04/2014 - 12

View 6 Replies View Related

Queries :: Display Date / Time Based On Weekday

Jul 7, 2015

I have a query which is displaying a date/time field a record was input. in my query I'd like to only display the records where the date/time based on what today is i.e. Monday it will display <Friday or yesterday for Tue to Fri.

View 7 Replies View Related

Prevent Duplicate Records Based On Multiple Fields

Nov 15, 2005

how or what function (DLookup) should I use to prevent duplicate records based on multiple fields? I want to look at data in three fields that can't match existing data in those three fields. It's ok if one or two of the fields match but not all three.

View 2 Replies View Related

I'm Sure This Is Very Easy: Update Multiple Fields Based On One Field...

Jul 26, 2006

I promise I have searched, but I hav spent 10 minutes reading through posts that are unrelated...

What is the code to have multiple fields updaterd based on what is input into a field?

---
Example:

A ZIP Code Field, which updates City & State on the form when entered. (I have a table that has over 39,000 ZIPs w/ City & State already there)
---



I have several applications for this, but if someone could explain this use to me, I will be able to figure it out.

Thanks a million!!!

View 1 Replies View Related

Tables :: Allowing Duplicates Based On Multiple Fields

Dec 23, 2014

I've been playing around with a new database design and ran into a possible 'error' that I would like to avoid.

It's going to be a payroll database to store time codes for hours spent working on specific projects. I have been struggling on how to put this together to fit with what we've been doing for years and I think I hit a few breakthroughs this morning.

However I want to avoid this error of possible duplication of entry.

Simple table set up - primary key is just a running integer; Employee ID; and Week Ending Date.

I can have multiple week ending dates for a specific employee; but I want to avoid having the same employee with the same week ending date. I cannot set up either field as being unique.

Quick run of data that would be in this table:

Code:
1 ABC 11/21/2014
2 ABC 11/27/2014
3 ABC 12/07/2014
4 DEF 11/21/2014
5 DEF 11/27/2014
6 DEF 12/07/2014
7 ABC 11/27/2014

in this example, when the last row is entered I need to get a popup or some warning that this time has already been entered.

View 13 Replies View Related

Forms :: Parametric Search Based On Multiple Fields

Feb 17, 2015

I want to create a parametric search for tools on the shop floor based on multiple fields [using VBA]

I've had a good search and turned up lots of useful things; but each example insofar has been fairly specific as opposed to a guide on how to implement it in a wider setting.

Let us say, for example, that my database has three fields each with a bound combo box: location; house-type; number of bedrooms

As I type in location, I want it to filter my results in the other boxes to that location (should be a simple case of applying a filter?) - which leaves me with all the house-types and bedroom-counts in that location.

As I type in the house-type, I want it to then narrow down the search again to filter the "location"+"house-type" to leave me with the available number of bedrooms.

What is the best way to implement this, given that I have about 20 fields*, some of which will inevitably be blank: as the user types in (or selects using combo boxes) the data required, it narrows down the search - and finally there's a "search all" button which returns all results matching the current criteria.

When I say "implement" I mean what underlying structure should I use. Is it best to create a query and update the query as items are entered, or would it be better to filter the form that's having the data entered?

The fields are organised sensibly into sub-forms for various characteristics (i.e manufacturing data, materials data, cutting data, etc, etc), don't worry - it's note a huge mess - and the code is all there to pull the data together as required!

View 10 Replies View Related

Database Update Of Multiple Fields Based In Lookup

Oct 28, 2014

I have two tables, one table (1) hold three fields, one of those fields is the master key (index). The other table (2) has a field which I want to lookup from table 1, that part is working. In the combo box I get all three fields displayed as I make my selection. I want to copy the other two fields as text at the time the lookup index is selected. I do not what the fields to automatically update each time the table is displayed. I have looked at update macros, I've looked at VBA.

View 1 Replies View Related

Reports :: Display Total Count For Multiple Queries

Sep 16, 2013

I am trying to build a report that shows the total count for multiple queries.

For instance I have a field in my table that can be either A, B, or C.

I have 3 queries built, 1 to show me the information for A's, 1 for B's, and 1 for C's.

I would like a report that shows me ONLY the total number of each category. such as:

A= 38
B= 72
C=12

Thus far I have only been able to create one that shows me all of the information from the queries.

View 4 Replies View Related

Queries :: Display Multiple Rows Into One Row Query Result

Mar 25, 2013

I have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.

Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.

I am currently using Access 2010.

View 5 Replies View Related

Queries :: How Can Query Display Info Based On Cascading Combo Boxes When Box (isnull)

Nov 27, 2013

I have a form with cascading combo boxes pulling from a table. They work perfectly, no worries. My problem now is if I do not enter information in every combo box (i.e. only two out of four combo boxes), how can I still run the query and get the appropriate information?

For example (these are my combo box titles in order):

Product
Type
Customer
Contract #

I don't want to necessarily look by Contract # all the time, but sometimes just by the general Product and Type to get a larger view. How do I set up expressions/criteria in my query to accurately produce that information? Right now it just produces a blank query table if I don't fill out all the boxes.

I've tried a couple of expressions with "isnull" criteria, but I must be doing it incorrectly.

View 2 Replies View Related

Queries :: Criteria To Display Records With Values >0 In Different Fields

Sep 24, 2014

i made a query to display students results after an exam. there are 12 subjects but a student chooses 8 from that. After an exam, the results are keyed in and a result slip is printed later. The problem is that the result slip displays all subjects including those not done by the student. I typed a criteria >0 in one of the fields and no records appeared. Is there a way that i can use to display only the subjects chosen and marks attained by a student and leave out the rest?

View 1 Replies View Related

General :: Make A Field Based On Data From Multiple Other Fields

Feb 19, 2013

Let's say I have a table called "Courses" which among other things has "Completed"(Yes/No) and "Category"(Text).

Now, there also another table "Progress" which has similar columns.

I want Progress.Completed to reflect if all courses of the specified category have been completed.

How would I do that? I'm new to access ...

View 1 Replies View Related

Reports :: Conditional Formatting On Multiple Fields Based On Two Criteria

Sep 24, 2014

I have a report which I would like to apply conditional formatting on multiple fields. I would like the conditional formatting to be based on two types of criteria

First criteria would be contracts that start with the year 2014. I used the following expression which worked - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Joseph Steinbok" or "Adam Godson")

To this expression I would also like to highlight fields which contracts start with 2014 AND have a particular Director assigned to it. For this I used the following expression - And [DirectorInCharge]="Name"

On their own, both expressions are working but I want to combine them. How do I do this? I've tried the following - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Name") but then nothing is highlighted. I also tried InStr(1,[ContractNr],"2014") AND [DirectorInCharge]= "Name" - in this instance EVERY record was highlighted.

View 4 Replies View Related

Queries :: Creating A List Based On Multiple Queries

Jun 23, 2015

I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error

ODBC-- call failed ODBC Driver SQLBase.....

Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.

Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.

View 2 Replies View Related

Reports :: Multiple Tables - Pull Information From Records Based On Certain Key Fields

Nov 5, 2014

I have a form (form1) that will populate with records (table1) based on key fields of "Project Number" and at time-points we will update part of the record, and then we create a word document detailing the updates with a couple of other bits of information not in original record.

Basically I want it to go from the original form (form1) into another form (form2) (via a command button) taking the key field of "Project Number" where it will ask the bits of information that we don't need have already and the contact person.

I have got this to store in a different table (table2) using a key field of just "ID" from the original records (table1). A list of contact people are in a separate table (table3) using a key field of "contact name" which will include other details for them such as address and phone number.

So from "form2" I would like to produce a report that contains information form "table1" based on the "Project Number" and "table2" based on "ID" as just been generated and "table 3" which is based on "contact name".

So I want to pull information form the records based on the certain key fields but struggling for it to pull it successfully...

Got relationships between:
"contact name" fields in both "table2" and "table3"
"project number" fields in both "table1" and table2"

In a query for the report I have all the appropriate fields, but only have the "contact name" and "project number" from "table2" is this right? or do I need both in?

Or would it be easier to draw all this information into "form2" but don't really want to select that many drop down boxes or would it be able to pull it from the "contact name" and "project number" boxes????

View 6 Replies View Related

Field To Automatically Fill With Correct Value Based On Information In Multiple Fields

Apr 16, 2012

I have a database in which we are trying to have a field "Headcount" automatically fill with the correct value based on information in multiple fields. The "Headcount" field will be based on about 6 or 7 rules. Most of the rules i can figure out, but im having trouble figuring out what the best way would be to search for a single individual's projects when they are on more than one project at a time. I thought about using a counter of sorts to count how many projects someone was one, but how do I pick them out of the entire table once i have found out the number of projects? Would a for loop work best or just many if statements?

View 5 Replies View Related

Queries :: Bring Forward Previous Day Balance

Oct 20, 2013

Table:

Date Item Quantity
------------------------------------------------
10/20/2013 Item-A 2
10/20/2013 Item-B 4
10/21/2013 Item-A 2
10/21/2013 Item-B -1
=====================================

On 10/22/2013, if Item was selected, I want to bring forward previous day balance as of 10/21/2013 for Item-A = 4 or Item-B = 3.

View 6 Replies View Related

Queries :: Forward Previous Balance Of Item

Aug 22, 2013

I have a table composed of the following fields:

1. Entry_ID:
2. Item:
3. Previous_Balance:
4. Increase_Or_Decrease:
5. Current_Balance:

Once the selected item had been entered, i want the Current_Balance of previous entry of that item to be forwarded automatically in Previous_Balance field. Is it possible? I have at least 100 types of items.

View 4 Replies View Related

Queries :: Method For Combining Crosstab Queries With Same Criteria From Multiple Fields?

Dec 2, 2014

I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?

QUERY1

Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT

[code]...

View 2 Replies View Related







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