Tables :: Calculate Some Additional Data And Generate Quotations - Too Many Fields

Jun 2, 2015

I have a database that will take lots of data entered by an employee and calculate some additional data and generate quotations. Within each quote there is a possibility for 15 different metals (5 Precious Metals, and 10 Base Metals). There is also 5 fields that need to be filled out about each Metal (What the metal is, the market being used, weight, whether it is included in a different price, and the price). I currently have 75 fields to address each Metal and their 5 fields respectively. Is there a better way to Normalize this data, and accomplish what I need accomplished? I want at the form level the employee to tell the database whether they want to add a Precious Metal, or Base Metal, or Move on to other data entry.

Here is a Screenshot of the design view of one of my tables with too many fields : table screenshot1.PNG

View Replies


ADVERTISEMENT

Tables :: Autofill Additional Fields From AutoNumber

Jul 8, 2014

I'm creating a database for a travel agency. There are 2 tables, one for customers and one for their bookings. I have established a relationship between the tables so the AutoNumber for customer ID links to a field on the add booking table.

In practice though, it would be easier to enter the customer NAME into the booking table and have it retrieve their ID that way to link them. This seems like I am missing something simple but can't fathom a way to do it.

The other factor is obviously more than one customer will have the same name so I may need to link first name as well to differentiate?

View 7 Replies View Related

Tables :: Merging 2 Fields Together To Create Additional Field - Unique References

Jul 23, 2015

Is there a way of merging 2 fields together to create an additional field

my database consists of 4 main tables (in order of relationships)

*HeadOfficeDetails
*SiteDetails
*ContainersOnSite *Contracts2015-2016

For example;

Account Reference: TEST
Site Number: 001

and the field i would like to have;

Site Reference: TEST/001

I would also like that when i add a new site to that account i will have TEST/002....

View 4 Replies View Related

How To Calculate Fields In 2 Tables In VBA

Apr 23, 2013

I have 2 tables - Customer and Carrier.

Each table has 3 headers (fields). Address, Latitude and Longitude.

I would like to know, how I take the value of the field (i.e Latitude) in each table for my calculation?

In Excel, I can specify the column (i.e Range("B" & x) - where 'x' is the row number)...but how do I do it in Access.

Code:
Dim db As DAO.Database
Dim rsCustomer As DAO.Recordset
Dim rsCarrier As DAO.Recordset

Set db = CurrentDb
Set rsCustomer = db.OpenRecordset("Customer")
Set rsCarrier = db.OpenRecordset("Carrier")

[Code] .....

View 2 Replies View Related

Queries :: MIN Function With Additional Fields Displayed

Feb 5, 2015

I am trying to return the MIN in a numeric field (single result) from my database and I have no issues doing this BUT the problem is that I also need to display additional fields in order to make the result useful. The additional field can't be grouped by b/c that changes the result of the MIN function.

View 3 Replies View Related

Modules & VBA :: Transfer Spreadsheet And Append Additional Fields

Jun 1, 2015

I am trying to use transferspreadsheet to import access worksheet and then I want to append additional fields. I am able to import the excel sheet into access, but need appending the other fields.

Code:

Dim fd2 As FileDialog
Dim xlapp As New Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook
Dim db As DAO.Database
Dim tb2 As TableDef
Dim fdx, fld As DAO.Field

[code]...

View 4 Replies View Related

Modules & VBA :: Creating A Record And Then Updating With Additional Info In Various Fields

Apr 24, 2014

In the code below I am creating a record with the INSET INTO statement and then Updating with additional info in various fields. it is not working the way I thought it would, so I am trying to create the record in it's entirety.

Code:
SQL_Grade_GUSD_ID = "INSERT INTO Grades (GUSD_Student_ID) VALUES (" & Me.GUSD_Student_ID & ")"
SQLM1_1_ELA = "UPDATE Grades SET Grades.Subject = ""BM1(ELA)"""
SQLM1_2_ELA = "UPDATE Grades SET Grades.Type = ""Exam"""
SQLM1_3_ELA = "UPDATE Grades SET Grades.Score = ""0"""
SQLM1_4_ELA = "UPDATE Grades SET Grades.Nam = ""GUSD BM-1"""
DoCmd.RunSQL SQL

[Code] ...

I am running to syntax problems when I try to USE the INSERT INTO to create the record with all the info in one statement.

Code:
SQLM1_1_ELA = "INSERT INTO Grades ( GUSD_Student_ID, Subject, Type, Score, Nam ) " & _
"SELECT (" & Me.GUSD_Student_ID & ")"" AS GUSD_Student_ID, ""BM2(ELA)"" AS Subject, " & _
"""Exam"" AS Type, ""0"" AS Score, ""GUSD BM-1"" AS Nam " & _
"FROM Grades"

I am Getting this error:

Syntax error (missing operator) in query expression '(12345)" AS GUSD_STUDENT_ID,
"BM2(ELA)" As Subject, "Exam" AS Type, "0" As Score, "GUSD BM-1" AS Nam From Grades'

View 3 Replies View Related

Importing Additional Data

Sep 19, 2005

Can I import data into a table that already has data in it? I want to add this new data to the existing information.

View 2 Replies View Related

Add Additional Number To Data

Oct 15, 2007

I have a table column with fixed data of "ABC-123" and need to add a zero to the number like "ABC-0123". How can I do it to all the data? Thanks.

View 2 Replies View Related

How To Link Additional Data Source To A Form

Mar 22, 2006

Hi All,

I have a db with three linked (related) tables. I have a form that has, at present, only one of these tables as a data source. Now I want to bring a couple of fields from another table into that form.

I know how to link a table to two data sources when first creating a table. But how do I do it when the table already exists?

I tried Table Design View | View Properties | Data source,
but that allows only one data source. If I link to another, the first disappears.

Thanks.

Adrian

View 2 Replies View Related

How To Link Additional Data Source To A Form

Mar 22, 2006

How to link additional data source to a form

Hi All,

I have a db with three linked (related) tables. I have a form that has, at present, only one of these tables as a data source. Now I want to bring a couple of fields from another table into that form.

I know how to link a table to two data sources when first creating a table. But how do I do it when the table already exists?

I tried Table Design View | View Properties | Data source,
but that allows only one data source. If I link to another, the first disappears.

Thanks.

Adrian

View 4 Replies View Related

Reports :: Save Additional Data From Report To XLS

Oct 25, 2014

I have a form where user can chose his own criteria for the report, like time period, ppl involved and other parameters. i open report filtered with these conditions. I also create a nice text (as public variable) bout what where the search criteria and put it in the header/footer. Then I export the report as xls file. it's nicely saved as column names and data, just what i need.how to add a field that would store the criteria, ie that string public variable that has it all neatly explained?

View 1 Replies View Related

Reports :: Getting Additional Data As Well As From Query / Table

Nov 28, 2014

I have a report which is bound to a query.The body of the report comes from the query.I also have additional data that is displayed on the form that is currently 'hard' coded into an a label.

I would call this data reference data. It's not specific to a client so there is no logical place to store it other than a reference table which will have no links to other tables. ie it's not truly relational. can I get data from this reference table in the same way I could do on a form by using a combo box and having a SELECT statement?

View 10 Replies View Related

Tables :: Set Up Validation Of Fields Unit And Size Based On Another Fields Data?

Nov 18, 2013

Basically in my order details table i have the following fields

Product
Unit
Size

At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie

ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big

When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.

How do i set up validation of the fields Unit and size based on another fields data?

View 4 Replies View Related

Tables :: Calculated Fields As Data Type In Tables - Calculating Total?

Apr 23, 2013

I am using calculated field as a data type in access 2010.

They are working fine.

However, I added a new field and now the final calc won't work.

I have Subtotal adding loads of fields together. Works fine.

Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.

The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?

View 2 Replies View Related

Queries :: Extract And Replace With Additional Data Append Delete Query

Jul 1, 2015

I have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record

EG

ID1,10,write a report,2400

I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:

ID2,5,write a report,1200
ID3,5,write a report,1200

My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.

View 1 Replies View Related

Quotations In DLookup Statement

Oct 14, 2012

Table1 has three fields. I'd like to use the following DLookup statement:

DLookup(“[Field1]”,”Tablename”,”[Field2]=Left([Field3],4)&’55’”)

Note that in the criteria, I want Field2 = the first 4 characters of Field 3 + the string value 55.

I am sure I've got the quotation marks wrong.

View 1 Replies View Related

Using Two Fields In A Form To Generate A Third Field

Oct 9, 2015

I've got a form where I want to add an employee and I have three fields (EmpFirstName), (EmpLastName), and (EmpCode).They all have their own control source from a table but what I want is EmpCode to automatically fill in after they type a first name and last name and not be editable. Smith, John and the empcode will fill in with smijo (3 of last and 2 of first). I understand this is probably completed using Expression Builder (not sure on the command) and how do I make it so Empcode still receives the data entered??

View 2 Replies View Related

Forms :: Generate A Subreport Base On Two Fields?

Jun 10, 2015

Ok so I want to generate a subreport for my deacons that show what families have been assigned to them.

Using the wizard I created a subreport that pulls the name & phone number(s) for each member with the same family ID, and Deacon ID (which pulls the name, number and FID from the member table, then checks the family table and gets the deacon ID). My question is, how do I tell the report to check if the current member I'm looking at is a deacon and if so use their deacon number as the reference number for "Deacon ID"?

I have a table for my deacons that links the member through their EnvNum (if they're a Deacon). Not sure if I'll need that table for this, but thought I'd mention I do have one.

View 2 Replies View Related

Modules & VBA :: Database For Quotations - Modifying Sequential Numbers

May 29, 2015

I have a database for quotations. The database automatically generates a new quotation number every time a new quote is started. This works great, and I am very happy. My next task is to allow the employee to pull up a quote that has already been generated and edit it. I would like for the new quotation process to be followed step by step, but with all of the information already filled in.

This will allow for any edits that need to be made, and keep from having to re-enter a lot of data. I want one thing to change, which is the QuotationNumber. It is currently formatted by "yyyymmdd-01" for the first quote generated on that day. I want the edited quote to have a QuotationNumber formatted by "yyyymmdd-01a". For every edit that letter change going through the alphabet in order. How would this new QuotationNumber code differ from that of the Other?

QuotationNumberCode.PNG

View 10 Replies View Related

Need To Match Data In All Fields In 2 Tables

Apr 14, 2008

Hi,

I need to match the data in all the fields between 2 tables.

eg.
Field 1 (Table 1) = Field 2 (Table 2)
Field 2 (Table 1) = Field 2 (Table 2)
so on......

Using the wizard, I'm only able to match 1 field at a time and this is very time consuming as I have 45 fields to match. Any advise? Can this be done via SQL?

View 2 Replies View Related

Tables :: Copying Data From Fields To Another?

Dec 5, 2013

I have a field that has a description of the project I'm working on, which has client information on it. I want to take all 600 of those records and copy them to fields in the same table, so that I can remove the client information, which will allow me to pull whichever of those two I need when I run a query.

View 3 Replies View Related

Tables :: How To Receive Data From Two Fields

Feb 15, 2014

now i have two tables,

Table 1, containing Purchases Orders (P1,P2,P3,.......)
Table 2, containing Local Purchases (L1,L2,L3,L4,.....)

Now i want to make a thrid table that contains a field for all purchases in table 1 & 2, (P1,P2,P3,L1,L2,L3,L4,.....), if i make a lookup wizard that will retrieve only from one table but i want to retrieve data from the two tables.

View 2 Replies View Related

Populate Fields Using Data From Different Tables?

Feb 6, 2012

need to create a database for work. there would be a way to select an id based on a previous table and have parts of it populate in this new table.

For example:

Table A - Webinars (Webinar ID, Webinar Title and Client)
Table B - Sessions (Session ID, Date, Webinar Title, Time, Facilitator, Session Occurred, Reason, Conference Call) [Webinar Title I used a look up field and just select the corresponding Webinar Title and it works nicely]
Table C - Users (User ID, Status, Prefix, Last Name, First Name, Email Address, Component, Role and State)
Table D - Registrants (User ID, Session Date, Webinar Title, Attended).

So in Table D I would like to add Role and Component, so when I select User ID #1 the Role and Component Field populate with the info based on Table C.

View 3 Replies View Related

Tables :: DLookup Between Two Tables - Calculate Product Price?

Jul 30, 2014

I'm trying to do a lookup between two tables, whereas table A includes the product ID and table B includes the price. Is it possible to create a field in table A with a DLookup function on the product ID to get the product price from table B?

Whenever I try to type in the DLookup function, it does not calculate.

View 2 Replies View Related

Modules & VBA :: Generate Tables At The Click Of Button?

Dec 9, 2014

I am required to write a code that will allow me to generate a table at the click of a button. Is there such a thing?

The table name should be Table1, and it contains 7 columns.

First column: Auto number (PK)
Field1: Memo
Field2: Memo
Field3: Memo
Field4: Memo
Field5: Memo
Field6: Memo

View 2 Replies View Related







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