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 Replies


ADVERTISEMENT

Reports :: Pull Information Based On Group Field?

Jul 21, 2014

My report is grouped by a Billing Group #...within each billing group there are sub invoice numbers that are tied to the main billing group # (they all have the same billing group # but different general invoice numbers). I need to pull data if one of the general invoice # invoice dates is greater than or equal to a specific date entered.

In other words: If a Billing Group # and one of the Invoice Dates is greater than or equal to a specific date entered then pull ALL GROUP data to report.

View 14 Replies View Related

Queries :: Select Query To Pull Records Based On Multiple Strings Entered By A User?

May 1, 2013

Is it possible to run a basic select query to pull records based on multiple strings entered by a user?

I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'

This allows the user to input one MMDBID and the records are retrieved from the db.

I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.

But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above

SQL statement (Like) is below :

SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));

View 10 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 :: IIF Statement Query - Pull Information From A Table Based On Specified Weight

Aug 4, 2015

I am having a problem to get my query to run properly. I have a huge IIf statement that doesn't seem to be working but I am not sure what is wrong with it. What it is supposed to do is return a good, actionable, or poor based on a specified weight and an actual weight. So what the query does is pull information from a table based on the specified weight and then using that information it should give back the correct rating in the last column.

Here are the conditions:

Poor:
AW(Actionable Weight)<Lower Actionable Weight
AW> Upper Actionable Weight

Actionable:
Lower Actionable<AW<Lower Acceptable
Upper Acceptable<AW< Upper Actionable

Good:
Lower Acceptable<AW<Upper Acceptable

The lower acceptable, actionable, and upper acceptable, actionable are different numbers based on the specified weight entered.

Here is the IIf Statement I currently have and I wasnt sure if order mattered or what I was doing wrong..

Rating: IIf([LOWER ACTOINABLE]<[Actual Weight]<[LOWER ACCEPTABLE],"Actionable",(IIf([LOWER ACCEPTABLE]<[Actual Weight]<[UPPER ACCEPTABLE],"Good",(IIf([UPPER ACCEPTABLE]<[Actual Weight]<[UPPER ACTIONABLE],"Actionable","Poor")))))

View 6 Replies View Related

Queries :: Query To Pull Information From Two Date Fields Into One

Jan 21, 2014

I need to know a query to pull information from two date fields into one. I have a BuriedStartDate field and an AerialStartDate field. In a third field I have PlacementStart. I would like to put the date into the PlacementStart field which ever is the oldest date bewtween the BuriedStartDate and AerialStartDate. It has been awhile since I have done any queries on Access 2007. I am completely self taught in Access.

View 2 Replies View Related

Tables :: Pull Information From One Table To Another

Nov 19, 2012

I am brand new to building a database.What I want is a database to store Quote, Job and Invoicing information. We receive quotes first and then they can, but don't always, turn into jobs. We can also receive a job without quoting it. We currently have two spreadsheets. One is for Quotes and the other is for Jobs.

QUOTES INFORMATION
Quote #
Date
Customer Name
Part #
Part Name
Quote Due Date
Qty
Lead Time
Price
Unit

JOBS INFORMATION
Job #
Qty
Quote #
Customer Name
PO#
Part #
Part Name
Est Hours
Start Date
Due Date
Price

As you can see a lot of the information in the Quote spreadsheet is also used in the Job spreadsheet. (Bold represents duplicated items) We currently type the information into each spreadsheet.

Then there are different forms that are filled out for quotes and jobs that contain the information in the spreadsheets.Is there a way that I can have the QUOTE Table automatically populate the JOB Table information?

View 6 Replies View Related

Reports :: Pass Listbox Parameters To Pull Multiple Separate Reports

Nov 23, 2013

I have a form that the user can add Work Order numbers to a text box and pass them to a listbox to collect 1 or more values. Each of which need a separate report with the labour hours for each Work Order.

I am having issues figuring out how to get it to pass them to a query or filter the reports.

I have tried many different examples and nothing seems to work.

View 4 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

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

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

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

Reports :: Can't Pull Up Report Multiple Times

Jun 14, 2013

Here is my delima, I have a report that I am able to type in a keyword and it will pull up that report exactly as I type the keyword, however, let's say that I want to pull up someone else's report I cannot. I have to exit out of my report and then go back into it and resubmit the new search.

View 13 Replies View Related

Reports :: Query Based On Linked Tables - Report Produces Multiple Copies Of The Same Record

Jul 22, 2013

My report produces multiple copies of the same record. I know why, but don't know how to fix it.

EmployeeTable.

With a one to many relationship with TrainingTable (via employee PK as FK in trainingtable).

Training table has a one to many relationship with a table called Range.

Report is based on a query that picks up the Employee/Training/Range (range just describes the training unit).

However, If I have more than one range expressed organized a training unit, the report spits out several copies of the Employee record to display all the ranges.

View 2 Replies View Related

Reports :: Joined Tables - Returning Multiple Records

Apr 10, 2014

I a report based on query based on joined tables. Im using FK and PK accordingly in the tables and have a junction table.

I've attached a sample (removed all unique identifiers for privacy in case you're wondering).

Table structure as follows:

Employees tbl - Junction (License Link tbl) License No tbl, License Class tbl, Endorsements tbl

So the query runs on all of these tables.

When I build a report on the query, Simon shows up Six times. I suspect because he has six endorsements.

How do I produce a report on this table/query structure so that I get:

Simon.
Class, 1,2,3,4,5
Endorsement F,R,T,W,DG

View 1 Replies View Related

Reports :: Counting Test Records From Multiple Tables In One Report?

Apr 22, 2014

I have been trying to create a report to count equipment tested between two dates:

I am using Access 2000 (old I know) and I have 21 different tables with the fields laid out the same. For the moment, I will list five of the tables: Servers, Laptops, Printers, Workstations, and Monitors. The criteria I draw from each table are the fields Model#, Part#, Serial#, Test Date, Retest Date, and Technician.

I can create a report from a query (say laptops). The criteria I is BETWEEN[Enter Start Date]AND[Enter End Date] under the TEST DATE field. Works great! In my report I use =Count(*)&" "&"Unit(s) tested" & "Between "&[Enter Start Date] & " and" & [Enter End Date].

That works great too.

I am trying to create ONE REPORT using ONE Date range and display how many units were tested:

Example:

Units Tested between March 1, 2014 and March 31, 2014

Servers 9
Laptops 23
Workstations 15
Printers 18
Monitors 31

If I can get these five tables, hopefully I can add additional tables to the same report as I need them. I am not savy with VB code, but will give it a go with some direction as to where to put it and how to add to it.

View 5 Replies View Related

Pull Out Unique Records Form Two Fields

Sep 23, 2007

I have this basic question (obviously not so basic for me): how to pull out only unique records from two fields.

More details - two fields with names of competitors in a tournament (Winner or Loser) and i need to build a query to have all players names participating in the tournament regardless if they have won or lost in one list.

View 4 Replies View Related

Queries :: Pull Data From Fields In 2 Different Tables

May 6, 2013

I have a query that pulls data from the following fields in 2 different tables:

Area1FloorPrep (tblFloorPrep) ex. remove ceramic tile
Area1Size (tblInstallationAreas) ex. 20 s/f, or just 20
Area2FloorPrep (tblFloorPrep)
Area2Size (tblInstallationAreas)....

All the way to Area20 (Floorprep and InstallationAreas) for both tables. I have created an installer invoicing form that pulls the data from the workorder that these fields are located in, but the problem I'm having is that I don't know what kind of query to create to concatenate the data in the 20 fields and concatenate the size of the areas next to the appropriate concatenated floor prep description. Is there a way to do this without coding?

View 3 Replies View Related

Queries :: Creating Query To Pull Info From Multiple Tables

May 7, 2013

I have DB used for inventory for many different categories. I have a table and form for the following: Location, Printers, Pc's and many more.

What i am trying to accomplish is to have a advanced search form that will display how many pc's and there makes and model from selecting the location name or Cost center from a combo box.

So an example would be I want to select MPP-WDF from the combo box click a button and it will return the number of PC and there makes of model's and some other information in a list of records.

View 9 Replies View Related

Queries :: Pull Records From 2 Tables If Lastname Is The Same

Nov 19, 2014

I have 2 tables with the same field names, but different data. I need to check if one person is linked to different companies.Both are linked with CompanyID. When I look for a person via the last name (field is called LastName), it should show me from both tables the rows that this person is in. how can I construct this kind of query?

P.S. I want to select all the not just a few from both tables

View 2 Replies View Related

Reports :: Grouping With Information From Multivalue Fields

Feb 6, 2015

I have a table which now contains a couple of hundred records with more than thirty fields each, and will ultimately contain over 1000 records. Some of these fields use the multiple value feature, and the fields and the forms which fill them work beautifully. Now comes the challenge.

Without going into detail that I'm not at liberty to share, I can say that there are different offices which have different people who are responsible in different ways for the work covered in these records. It is possible for each record to have multiple people assigned to it from the same office. This requires setting the control that shows the people from each office to allow multiple values. I need to be able to create a report which will allow me to hand a list of all the records each person is responsible for in the office to the person responsible, in spite of the fact that this will mean records will show up on more than one person's report.

Before I knew that there might be multiple people in the office for each record, I created great reports grouping and ordering by this office's control and field. Now, since they allow and store multiple values, I can't use them any more. I need to be able, as I said above, to get the same effect. I think the answer lies in some sort of calculated field or formula that applies text filters. What I need to be able to do is look in the field for this office and see if a person's name is contained in the field for that particular record, regardless of who else might be in the field, too. I need to be able to use the results from this filter or calculation or formula to generate something I can use in the group and order by processes.

View 7 Replies View Related

Fill Multiple Fields With Same Information

Jul 22, 2013

The database needs to have columns for 17 individual connectors. These fields are populated from another table containing all of the possible connectors that could be used and a description of them. I need to set up a query that contains the 17 connector fields as well a field with the description of each connector. Is it possible to have this query auto populate the description fields from the table that contains the connectors. I have attached a picture below.... Each pin has a label that has to be manually filled and a pin name that has to be chosen from another table. I want to have the pin description fields auto populate to match the selected pin names.

View 4 Replies View Related

General :: Recording Information In Form - Filling In New Fields Based On ID

Jul 26, 2012

I have a form to record student information. On my student table i have a school I.D that links to the primary key of my school table.

My problem is that in my form, i want to be able to fill in the rest of the fields about the school info based on the I.D chosen.

i.e.

student table:
Student id
name
address
school I.D (FK)

School info table:
School I.D (PK)
school name
contact name
email
address

My form that's linked to the student table needs all the information from the school info table in separate fields
but i can only select School I.D in the Control Source Property.

View 1 Replies View Related

Queries :: Joining 3 Tables - Using Key From One Table To Pull Back Records From Another

Jun 29, 2013

I've got a query that uses a key from one table to pull back records from another.

My question is...

On the table with the key there is another field that I want to use to pull back data from the same table from above.

A bit more info...

Table one holds colleagues details
Table two holds records

Within table one there are 2 different fields that hold different ID's for the same colleague, currently I run two different queries to pull all data - I want to know if I can simply run one query that will return every thing?

Schedule an import or export operation

View 11 Replies View Related

Changing Information (Same Info) In Multiple Tables

Apr 7, 2015

I have now been asked to create 'something' (form, query, whatever) that will:

Allow the engineers to change the date in multiple tables for parts; and,Change (in)activity status information in those tables; and,Delete the same obsolete part(s) from two tables and add that information (one entry) into another table.

View 1 Replies View Related

Creating A Form Which Updates All Information In Multiple Tables

May 17, 2005

is there anyway you can create one form which updates all the information in multiple tables. I need to create one of these to make the navigation of the database more easy, so if anybody could help me it would be much appreciated.

thanks.

View 1 Replies View Related







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