Multiple Table AutoLookup
Jan 3, 2005
I am trying to pull information from 2 seperate tables and store that information in a 3rd table. I need to autofill information from both tables when I select the information from the field that joins them. Then that information needs to be saved in the 3rd table. I have to store this "duplicate" copy of the informaion becuase one table's inforamtion changes quite frequently and I needs to be able to find historical data. Here is a simplified version of my tables for example.
Products: ProductID (KEY), ProductDescription
Quote: ShipID (KEY), ProdcutID(KEY), Price (changes per ShipTo)
ProdutsOrdered: OrderID (KEY), ShipID, ProductID (KEY), ProductDescription, Price
Again, all information has to be stored in ProductsOrdered because things like Quote.Price can change frequently.
Another important feature is I have to beable to add records with what ever method I use. This Is being done in a sub form so that many products can be placed on an order form. I can get the tables to combine in a way that will allow me to view the information, but not add to it.
View Replies
ADVERTISEMENT
Apr 28, 2005
I apologize for asking yet another question about autolookup but I have poured of Pat Hartman's example and all the posts in this forum and still can't seem to make it work. I have attached my db. I want to type in the asset number and autofill the rest of the form. Can someone please look at this and tell me what I am doing wrong. Thank you very much.
View 2 Replies
View Related
Nov 5, 2004
I am building a purchase order database that derives its subcontractor information from a separate table.
I want to be able to drop down a list for the subcontractors and once selected, the entire subcon area will be populated.
Another section of this PO is the project name, etc., which is a similar scenario, which has to be populated in the same manner.
Word file shows the structure of the PO.
Any help would be greatly appreciated!
jan.bodlund@shaw.ca
View 5 Replies
View Related
Feb 23, 2006
I'm trying to establish a ticket/citation database for a college security office at which i work at. I have two tables, table 1 includes the ticket information (date of ticket, citation number, *tag #, amount etc.), table 2 includes Repeted offenders information such as (name, address, city, zip, phone number, student ID# etc.).
I would like to have a table or query(preferably table) which automatically matches tag#'s to student information as soon as a new ticket is entered, *if the student information is available.
Thanks a lot in advance
View 3 Replies
View Related
Mar 20, 2005
I was just wondering if anyone knows how to sort of mask an ID as text. For example in this assignment I am doing at university I have a field named Flower ID which is a number. However, instead of displayign that number in forms and tables I want it to display its equivalent Species and Variety of flower. Is there anyway to fix this? Thanks for your help.
View 2 Replies
View Related
Apr 24, 2006
I'm getting very frustrated with trying to get this working. I've tried following other suggestions in this forum but must be mucking up on one vital peice.
From a choice in a combo box I want other values filled in on a form.
Can someone link me to a small, easy to follow example? I think I've got the query part sussed but fall down on the form side.Cheers:(
View 5 Replies
View Related
Jan 5, 2006
Hi there!
It's been almost 10 years since I've done any work with databases, so I'm really REALLY rusty and I barely remember how to do anything. I was wondering if you guys could help me out with a question I have in making a form.
I have 2 tables. The first table has a list of names and ID's. There exist some duplicate names, as some people are associated with more than one ID.
And there are two fields to the ID. One is Called the Client Code, and the other is the Client Division.
So the 1st table, in datasheet view, is basically:
# | Last Name | First Name | Client Code | Client Division
The second table is a list of addresses based on Client Code and Client Division:
# | Client Code | Client Division | Address 1 | Address 2 | Etc
----
What I'm trying to do is the following:
I'm trying to create a form such that as I'm typing in a last name, it does autolookup and auto filling. I may have multiple people with the Last name of Smith, so then it dynamically creates a dropdown box with all possible unique first names associated with the last name Smith. And after I choose/type in the first name, I get to two more dynamically created drop-down boxes that list all possible Client Codes and Client Divisions associated with this name. After choosing that, I figure it's a simple autolookup to populate the address fields.
Does anyone have any insight on how I can get this done? Another piece of functionality that I'm trying to work into this is that if the name doesn't exist or if there's a new Client Code and Client Division or a new address, the data gets automatically added to the respective tables.
Does anything of what I wrote make any sense?
Any help would be greatly appreciated.
thanks
Imants
View 1 Replies
View Related
Jun 16, 2006
I have an autolookup that changes the numbers 2, 4 and 6 into P, M, and D so they look easier for my purpose. However, when I use a select query to find the max number for each criteria it shows a number not the letter. Do lookups stop working when you apply maths or can i change this.
View 6 Replies
View Related
Dec 13, 2012
I am using Access 2010 and Excel 2010. I need to have VB script to export the access table 502 records by 38 fields into Multiple Excel workbooks each having multiple tabs. In the Access table each record has two fields: Div and Tab that will be used to name each workbook and each tab (sheet). There are 6 unique "Div"'s to name the 6 workbooks and there are several "Tab" names for each Div (workbook).
Note: These 6 workbooks with multiple tabs were originally imported into Access from one common folder on my desktop by this routine:
Option Compare Database
Option Explicit
Private Sub Command1_Click()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
[Code] .....
View 7 Replies
View Related
Dec 13, 2012
I am using Access 2010 and Excel 2010. I need to have VB script to export the access table 502 records by 38 fields into Multiple Excel workbooks each having multiple tabs. In the Access table each record has two fields: Div and Tab that will be used to name each workbook and each tab (sheet). There are 6 unique "Div"'s to name the 6 workbooks and there are several "Tab" names for each Div (workbook).
Excel workbooks would take names from the "Div" field and the tab names would come from the "Tab" field in the Access table. First need to find workbook name (Div - Field) then the look for each sheet name (Tab - Field) to create 1st Excel workbook with all the sheets (Tab) and repeat the process. I think you need to approach of read the Access table one record at a time keying on the "Div" and "Tab" fields in creating each Excel workbook with the associated multiple tabs (sheets) that are written to a common folder.
Note: These 6 workbooks with multiple tabs were originally imported into Access from one common folder on my desktop by this routine.
Option Compare Database
Option Explicit
Private Sub Command1_Click()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
[code]....
View 12 Replies
View Related
Nov 16, 2006
I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.
THE FIELDS REQUIRED IN THE SESSION TABLE ARE,
Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made
The CSV data set attached
tHANK YOU
SIMON
View 2 Replies
View Related
Apr 12, 2013
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
View 1 Replies
View Related
Sep 21, 2004
I am trying to create a simple Search form in Access where a user can select a desired record and query multiple tables using the inputs.
I would like them to be able to query Retailers, Distributors and Products.
The 6 tables are linked as follows:
Although some of these tables are not included in the query, they are required to ensure relationships.
Retailers -- Uses (RetailerID,DistributorID) -- Distributors
Retailers -- Orders (RetailerID,ProductID) -- Products
All retailers have at least one distributor BUT a retailer may or may not have ordered any products.
I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:
Code:
View 5 Replies
View Related
May 28, 2015
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
View 8 Replies
View Related
Nov 10, 2014
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
What is the best approach?
View 3 Replies
View Related
Jul 24, 2013
I've got a table tblPatienten, a table tblRechnung and a form frmRechnung.
The primary key in tblPatienten is KundenNummer, the primary key in tblRechnung is RechnungsNummer. The relationship between tblPatienten and tblRechnung is one to many.
Now, every patient (stored in tblPatienten) is allowed to make multiple orders (stored in tblRechnung).
How can I assign each new order entry in frmRechnung to an existing patient in tblPatienten?
View 10 Replies
View Related
Sep 20, 2014
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
View 8 Replies
View Related
Sep 8, 2014
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
View 14 Replies
View Related
Nov 21, 2014
I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
View 2 Replies
View Related
Mar 6, 2014
I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?
How to get all the data on the reallocated form to save?
View 6 Replies
View Related
Aug 1, 2013
I have two tables
tbl_Orders
Cust OrderNo OrdLne Prod Qty
ABC SO123 1 AA 15
ABB SO124 1 AB 10
tbl_StockLoc
Prod Loc Qty
AA ZX12 10
AA ZZ12 10
AA ZZ13 50
AB CW12 15
One product can have up to 10 locations in the tbl_StockLoc
I need to print the following report (up to LOC10)
Cust OrderNo OrdLne Prod Qty Loc1 Qty1 Loc2 Qty2 Loc3 Qty3 ....
ABC SO123 1 AA 15 ZX12 10 ZZ12 10 ZZ13 50
ABB SO124 1 AB 10 CW12 15
View 1 Replies
View Related
Nov 25, 2004
Hi There,
I have a question which is not so easy to discribe but maybe easy to answer, so i'm giving my best shot.
I'm making something to store information about patients. If made the forms en table with the wizard within access and some extra functions with VBA. The first problem i have is this:
I have to many control elements on 1 form (control boxes, I get a error message) so I need to split this form up into 2 forms. Other control elements or no option, because the input should be like this.
The information form both forms need to be kept together in the linked table as 1 record. But now I have split the form up into 2 forms I get 2 separated records. 1 record with the information from the first form and the other record with information from the second form. My question is what can I do the merge both records to one or maybe better to make sure that both forms only generate one complete filled record, so my table won't grow unnecessary big.
Please keep your answer as easy as possible so I can keepup. Thanks!
Greatings,
Pascal
View 4 Replies
View Related
Mar 27, 2006
Hi,
I'm making a database of spare parts for machinery.
But I came upon the following problem :
A part can sometimes be used is more that 1 machinery.
But I do not understand how I put the ID of all the machines in 1 field of that spare part
example : spare partes :
- ID
- name
- machines
How do I solve this problem the best way?
TIA!
View 7 Replies
View Related
Jul 7, 2006
Well I'm hoping someone here can point me in the right direction. This is my problem... We've got about 50 Access databases scattered across the network. Each network database contains the same tables and fields, but of course, each one contains different information (one network database is for DepartmentA, another network database is for DepartmentB, another network database is for DepartmentC, and so on).
What my boss wants is a single table/view that displays all the information contained in every network database table. The other part of this is that my boss then wants to be able to make changes in this single, all encompassing table/view and have those changes be pushed back out to the individual network databases. Is this possible? And if so, can someone help point me in the right direction? I'm still a little new at Access, but I can manipulate VBA code and all that if need be (I'm learning more and more as I get deeper and deeper into this project).
Thanks!
Lucid
View 2 Replies
View Related
May 4, 2007
I inherented a bunch of db from someone else, I've created / maintained db before, but this one has me stumped, the creater has a db, dbA, but dbB and dbC and dbD get the login info from dbA, has anyone done this? How is it done? I guess I get the concept, only one has to be maintained.....
would appreciate any ones thoughts/comments on this
View 3 Replies
View Related
Jan 16, 2008
I am currently creating a multiple table database, for a project, in which jobs done can be entered.
Here is how it i set out
CustomerInfo (Name, Address, ID (autonumber), etc)
Rooms (Information on colour, paint on room walls)
Ceiling (info on colour and paint on ceiling)
Woodwork (info on colour and paint on all woodwork)
Dates (starting and completoin date, month and year)
I have set all the table relationships throuhg the id, yet the id is never the same (on customer info it is 5, whilst everywhere else it is 2) I want it so that it is forced to one I.D. I have set up a form to enter all this information in. It works well, and allows you to just enter information, as no previous info is dispalyed.
Now however, I need to be able to search the whole database (by either the month/year in the dates table, or by the unique ID, first name, last name etc.)
How can i set this up in access? It will need to search all forms, and needs to be set up as either a form / whatever is easiest, but can be a new form, as long as I am told how to.
So, how do I solve both of my problems?
TIA!
View 5 Replies
View Related
