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 Replies


ADVERTISEMENT

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

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

Tables :: Pull Field From Main Table And Create A New Table With Date Stamp

Nov 4, 2014

I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).

Currently, I setup the query to pull info from the form field like this:

DateField: [Forms]![frmmain]![DateField]

However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).

View 6 Replies View Related

2 Tables With Same Value In A Field.. Pull Into A Report Or New Table? Possible?

Sep 24, 2007

I have a slight problem here with a database program I am designing. I have two excel spreadsheets, one created by one department here, and the other created by a second department.

I have the access program importing the relevant fields from both programs into 2 tables. Table 1 is the listing of employees in the company and their info (minus the location where they work). Table 2 is the listing of employees who are in this facility.

I need to prepare a report of all employees in this facility who appear on the other table.

I have a field in each table (both called F1) with the employee names.

Is there a way I can run a report in Access where it pulls all the info I need into a Report as long as the name appears in both tables F1 fields?

Even if I can create another table based on that info, and then just do a full dump to a report from that new table, that would be great.

I am not familiar with VB or SQL really, but I figure something like this would be doable, I just don't know how to go about writing it :

If Table1.F1 == Table2.F1 Then {
Append Table1.* INTO NewTable3
}

Something where if the name on F1 in Table1 matches the name on F1 in Table2, it takes all the data from that record and appends it or dumps it brand new into a NewTable3..

Any help is appreciated.

Thanks,

Bill

View 1 Replies View Related

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 :: 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

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

Tables :: Autofill Information In Invoice Table

Feb 11, 2015

I essentially created an account since I can't seem to find a straightforward answer much anywhere else (plenty of hints on auto-filling forms- but that isn't particularly conducive to my specific need on this).

I've attached a blank copy of my database.

The immediate concern I'm having is that I want to be able to autofill data in the invoice table based on the customer table and the products/service table.

*I want to be able to select a first name [or other primary key if necessary] to fill in the last name, company, address, city/state/zip, phone, fax, cell/alt phone, and email automatically... that is without multiple dropdown selections or input to those sections at all.

*I want prices to fill in to the 'cost of product/service X' so that I may use it for other calculations in the invoice table- as well as to make forms from it directly.

View 7 Replies View Related

Tables :: Information Populating In Another Table Form?

Jun 9, 2014

I have created two tables. One table list of 100 Categories that I monitor each month. The Categories are never changing month-to month, however, each might be associated with a different client month-to-month. Presently, I am manually typing in the Client information month-to-month with information pulled from the Client's table. The Client's table has a Category Field which is populated when a Client has purchased space to use it.

In essence, I have the Category Table (Fields: Record Number; CategoryName; Client Name; beginning date the client will use the Category and Ending Date the client will stop using it). The Client's Table has a lot more fields/information but it still has the same fields as the Category Table. I am trying to be able to use the Category Table and have it automatically populate with the client who is using the Category at that time. Any Category not being used by a Client then the Query should write "Open" in the Client's name field. I have tried many different scenarios but can't seem to get it to work. I must see all 100 Categories each time I run a report. It doesn't have to say "Open" but where ever a client did not use the Category it should be left blank.

View 1 Replies View Related

Tables :: Set Up Table To Add Information On Daily Basis

Feb 4, 2013

I am tracking extreme useage on account numbers. I've set up a table to add the information on a daily basis.

Currently the table has the following Fields:

Report_Date / Account / Usage / Sent / Received / Comments / Notes
(the Sent and Received fields are Y/N)

I've gotten a report to send working as I want, but I am running like 6 queries to put all the data together correctly. For example; if an account was flagged today and sent today I do not want to send another notice for the next 5 days; but I still want to log the data in the table for historical reasons... Again, this all works. Just that it is not pretty..

The part that I keep fighting is the responses. When I receive a response I want to log that. Right now, I check the Received flag, and add in any comments the customer sends back. This is easy. But I also want to print that information out on the report - just the latest information. For example, if I send a notice out on 11/1/12, 11/18/12, 12/14/12, and 1/10/13 and I have responses back from all of these logged in the table, when this account number gets printed again - say today - I want to add to that report JUST the comments from the 1/10/13 report.

View 4 Replies View Related

Tables :: How To Get Information Inputted On Form To Store In Correct Table

Mar 4, 2014

I'm having some trouble getting my information that I input on my form to store in my correct table. I will attach my DB so you can take a look at what I have thus far.

Here is what I am wanting to do:

I have TblEmployee, TblEquipment and TblJunction and FrmTracking and FrmUpdate

I input the bulk of my information thru FrmTracking, my trouble is I can not seem to get the information that I input in my FrmTracking to store in the correct table. I can get the information to store in TblEmployee, however the information that I want to be stored in TblEquipment will not store in there.

View 14 Replies View Related

Pull Unique Recs From Many-to-Many Related Tables

May 13, 2005

Greetings all! We have a mentor-mentee database that's been setup with a many-to-many design. We're storing essentially the same data in each table except for the field names. We thought we were good to go but one additional request was made to determine a count of employees participating in the program (regardless of whether mentor or mentee) in each section of the company. We don't want each person counted multiple times if they are both a mentor and a mentee, or if they have >1 mentee or >1. I created a couple of queries to pull unique empIDs from each table to narrow down each table BUT I'm stuck on being able to pull unique id's from both tables.

I first tried the unmatched query wizard from both viewpoints but then I don't get those cases where a mentor is also a mentee. In other attempts, I end up with a cartesian product. I've cruised through the queries section but haven't come up with any examples related to this type of situation.

I need a way to pull both unmatched records from each table while also getting essentially the first occurrence of instances where mentorID=MenteeId. I'm no SQL expert so if someone can think of a statement that would suffice, it would be greatly appreciated.

View 2 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 :: 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

Queries :: How To Get Query To Pull Data From All 9 Tables

Mar 13, 2014

I have 9 seperate tables - each of the tables has similar headers

Claim #
Agent
Pass/Fail
Request type
Record Date

I have built relationships between all the tables. I am attempting have a singular query be run based on start and end dates under "record date" . I have the criteria already set. But when I run the Query no information is pulled. How do I get the query to pull this data from all 9 tables?

View 2 Replies View Related

Tables :: End User Input To Pull A Specific Record

Dec 5, 2012

I want to use a Form or Report to have the end user enter say a Customer # or the Customer Last Name and then have Access pull and display that record so that the end user can than print all the saved information from that record.

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

Pull Data From Another Table?

Jul 27, 2006

I have a table that contains the following:

Code BrandName Lead Free Nickel Free
001 AAAA Yes Yes
002 AAAA Yes No

On a form the user selects the code field and in this table the field is called BRAND. On the same form, I need to display the value in the Nickel Free field so if selects 001, the field on the form needs to be "Yes", if the user selects "002", the value needs to be "No"

View 1 Replies View Related

Only Pull The Newest Date From A Table

Jun 23, 2005

I have a crosstab query that I ONLY need the newest date from the table I'm pulling from reguardless of what it is for. Here is what I have:

TRANSFORM Max([Cust Count].[Active Customers]) AS [MaxOfActive Customers]
SELECT [Cust Count].Node, Sum([Cust Count].[Active Customers]) AS [Total Of Active Customers]
FROM [Cust Count]
WHERE ((([Cust Count].Services) In ("core","data","telephone")) AND (([Cust Count].ASC)="uh") AND (([Cust Count].Date)=#6/12/2005#))
GROUP BY [Cust Count].Node, [Cust Count].Date
ORDER BY [Cust Count].Node
PIVOT [Cust Count].Services;

I know this is only setup to get items from 6/12/05 but more will be added to the table and I only want the new info for this query. Thank you.

View 11 Replies View Related

Would Like Field To Automatically Pull Data From Other Table

Apr 28, 2006

Hello Everyone,

I am having trouble with our receiving database. This database consists of two tables. One for vendors, which basically contains their vendor ID as well as vendor name and phone # etc. The other table is our receiving data table. When our receiving person receives product in, they log this in the receiving table including info such as date, autonumber for record, vendor etc. The problem is, when the person selects the vendor id, which is set up as a lookup field, we would like to have the vendor name pop up atuomatically within that record. I cannot for the life of me figure out why this isn't happening. The person is using a form to enter all of this data and runs a summary report at the end of the day. Our accounting dept. is requesting this info be added, but I cannot seem to figure it out.
Thanks so much for any assistance!
Amy (monet1369);)

View 6 Replies View Related

Search To 'pull' Data From Diff Table?

Mar 29, 2006

v sorry for the basic question, but ive been banging away at access and i cant my head around this..

i need to run a simple query. the query/ search will ask users to enter in the number of a document. i want, when this code is entered, for 2 controls on the form be updated with codes that are stored in a table based on the code they searched for originally.

heres my table structure...
Table A
Doc ID (PK)
Doc No (manually input and is the search item that users enter)

Table B
Unique ID (PK)
Doc No (FK)
Info (to populate field 1)
Info (to populate field 2)

how do i perform this in the query section? do i need to manually code the SQL required, or is this query basic enough that i can just select the fields required in the design view of the query?

thanks guys

View 1 Replies View Related

Forms :: VBA Code To Pull Data From Different Table?

Mar 26, 2015

I have a form I use for data entry, it needs to generate an id called RO Number and i need it be generated by access starting at number RO129036 and then keep adding sequentially, so RO129037 etc etc.

as i already have data in my DB that i need i cannot just reset any fields

having a table with just one field - the numeric part of the RO number so first one would be 129036 - so i need the form to pull this field, add 1, and then add "RO" at the beginning of it? i have really been struggling with this database

View 3 Replies View Related

General :: How To Pull Data From One Table And Store It In Another

Nov 10, 2013

I am trying to add an attendance records to my database but cannot figure out how best to do it..I already have a 'children' table were all the kids info is stored and have created a 'roll' table.

i want to be able to open a form and search first and/or last name from the 'Children' table, then be able to save both first and last names and the date into the 'Roll' table. (then open reports etc later based on dates)how to pull data from one table and store it in another.

View 3 Replies View Related

Queries :: Pull Invoice Number From Another Table?

Oct 22, 2013

I have 3 tables invoice, customer and items. I need to create a query that will get the next invoice number from the invoice table and mark that invoice number in the items table for a set customer where the invoice field in the items table is blank.

The invoice table is now set up as a autonumber, therefore I need to append to get the last new number.

The items in the item table are unique and this is why I want to mark the invoice number in the item table field invoice.

View 1 Replies View Related

Modules & VBA :: Pull Data From Query (or Table)

May 4, 2015

I have a table that has four columns. Column 1 had people's names, column 2 has their email, 3 has a category, and 4 has their office.

I have a form with radio buttons, When you check a radio button and press OK it will display the e-mails for the people selected. Problem comes when it's by category, since categories are not unique to one person. I want to click the category radio button and have it display every person under that category, concatenated with a comma.

Problem is I can't make a listbox because it will show the same categories several times instead of just once, so I want the radio button with the category name on its label, and then in the code I need to tell Access to go to the table and search for the e-mails from a specified criteria, the category.

How do I do this?

View 1 Replies View Related







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