Create Table From Truncated Info From Other Table
Oct 20, 2006
I'm a relative newbie to the more advanced features available to Access.
If someone could help me or point me in the right direction, I'd really appreciate it.
I have one table that contains companies and associated contact info. Each company can have more than one unique number ID (3 digit alphanumeric).
I want to take this table and run a query to create a new table that will only have one instance of each company name, along with the other contact information. The unique ID's aren't needed in this table (but it would be great if I could somehow toss them in, too).
This new table would then be used to create a form with a combo box containing all of the names to quickly jump to their contact info.
Any and all help would be appreciated. If a query isn't the best avenue for this, then please point me in that direction, too.
Thanks!
View Replies
ADVERTISEMENT
Oct 14, 2013
I have been using Access 2013 to make a database which outputs values to PowerPoint.
I have two tables with data in which are both brought together in a query which is the recordset my VBA code relates to to output it to Powerpoint. Both tables contain data in 'long text' fields. The data from one outputs into PowerPoint textboxes without any issues regardless of the length of the data but the data from the other one is truncated to 255 characters. It definitely says it is a 'long text' field - is there any reason it would truncate the output?
View 4 Replies
View Related
Jun 28, 2013
I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.
View 3 Replies
View Related
Aug 5, 2014
I have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
View 2 Replies
View Related
Aug 29, 2006
I have a form that I am working on.
When you open the form, it asks for a tool # (which will be a drop-down box). There will be a command button that will bring up a sub-form. In the subform, there are 2 combo boxes. One picks the group that the tool falls under, and the other has the section of the group for the tool.
When the user picks the section, I need it to filter the questions that pertain to that section (which is listed in a list table), and pull those questions, and populate the master table with them. (i.e., Pulls "where is tool?" from tblQuestions, and puts it in the tblQuestionMaster when section is selected from combobox).
:confused:
View 1 Replies
View Related
Jun 29, 2005
Hi
I am creating a database but I am having a problem with one little bit (so far!!)
I have form (and table) 1 and form/table 2. When I am in table 1 (which is company details) I want to be able to click a button which will open form 2 and allow me to enter a new record of incoming mail details regarding the Co. selected in form 1.
I have made a relationship between the Company ID on both tables.
I cannot seem to work out how to make Access take the Co ID no from form/table 1 and stick it into form/table 2 when I push the button.
Can anyone offer any advice??
thanks heaps
Siobhan. :p
View 1 Replies
View Related
Aug 31, 2007
I am trying to build a Form that will show an estimate (then eventually will be moved to a project if customer and employee aggree to price and project) in a Form F_Estimates is a M_Customers(Customer_ID) (Based on a Table) and thier info in a Subform. Also is the "projected costs" from parts out of the Parts(Part_ID) (Based on another Table) in a second Subform as a list that I need to calculate $$$ in
(Dang that still sounds evil and definately NOT understandable even after edit... so)
Here's some basic info
Tables
EstimatesandParts - Table
EstimatesandParts_ID : Autonumber
Estimate_ID : Number
Part_ID : Number
Parts - Table
Part_ID : Autonumber
PartNumber : Text (not a number due to some part#s have letters in them)
PartName : Text
Unit Price : Currency
Description : Text
Estimates - Table
Estimate_ID : Autonumber
InvoiceNumber : Text (again can have letters in it)
EstimateDate : Date/Time
EstimateTime : Date/Time
Employee_ID : Number
Customer_ID : Number
ProblemDescription : Memo
Customers - Table
Customer_ID : Autonumber
FirstName : Text
LastName : Text
CompanyName : Text
Address : Text
City : Text
Province_State : Text
Postal_ZIPCode : Text (CDN Postal codes are letter num letter...)
you can see the link table in the EstimatesandParts Table
Now I want to use that link to populate a subform in the F_Estimates form
Forms
SF_Customers - SubForm
(all boxes atm are text boxes on this form till I figure out the Parts section then will use same base for this so I can pick any customer in the database to be the customer for this estimate. Also will have ctrl button for making new customer with customer form and a refresh on Focus Gain bit of code)
FirstName
LastName
CompanyName
Address
City
Province_State
Postal_ZIPCode
SF_Parts - SubForm
Default View -Continuous Forms
(want it to be a list of parts that I can grab prices and descriptions from then in a bit of code to calculate a cost of parts)
Part_ID : Combo Box
Control Source - Part_ID
Row Source Type - Table/Query
Row Source - SELECT Parts.Part_ID, Parts.PartNumber, Parts.PartName, Parts.UnitPrice, Parts.Description FROM Parts ORDER BY Parts.Description;
(Pulls info from the table Parts for input into a list of parts to be used on that project)
PartName : Text Box
UnitPrice : Text Box
(here's where I run into problems due to the fact that the form is not based on the parts table but rather the link table EstimatesandParts so I can't propogate the info to the 2 other text boxes, ps I dont care if they cant be text boxes and have to be linked or some other type I'm not "set" just need to find out how to make it work )
(have tried a couple things to complete this task)
Me.txtPartName = Me.Part_ID.Column(2)
Me.txtUnitPrice = Me.Part_ID.Column(3)
(works AWSOME ... for ONE ROW then propogates the second selection to the first and second and third selection to first second and third and so on ...)
(tried to make control source for the txtPartName to)
=Forms!Parts!Partname
(Doesnt exist .. akkk, cant use ActiveForm either as it doesn't focus on the SubForm but the MainForm ... cry)
F_Estimates - Form
Estimate_ID
InvioceNumber
EstimateDate
EstimateTime
ProblemDescription
(all basic Text Boxes)
Employee_ID
Customer_ID
(Combo Boxes Select Customer and Employee from list of present ones of each)
SF_Customers
SF_Parts
(Both SubForms on the main form)
Now this is an Exerp from my entire Database I like to work on one small problem at a time and I have made this its own little database till I figure out the problem then I will bring the info I learn back into the rest of the database and go from there ...
Hope you can help I have a feeling I will need to make a recordset and go from there but I'm just not able to wrap my head around that for some reason
Thanks in advance for ANY and ALL help that I get from here
View 10 Replies
View Related
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
Jun 4, 2015
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
View 7 Replies
View Related
May 1, 2012
Here is what I am trying to do: I have form, and a table which is created from this form. The data is entered a second time using the form to make sure there are no discrepancies.
Any easiest way to implement this? (create a new table (which is duplicate), and then compare them?
View 5 Replies
View Related
Jul 19, 2012
I have two tables. The first one is as follows:
MP No (Primary key), Sales, Date Entry, Specs, Email
The second Table is as follows:
MP No, Quantity, Description, DWG, Price
There is a "one-to-many" relationship between the two tables through the MP No field so whenever I go to the first table there is a sub-table for each MP No. Correct?can I create an extra sub table under the sub table already created?
View 10 Replies
View Related
May 13, 2005
Guys i have tried to seach the Forum but can't find a solution to my need.
I would like to get a collection of table names from another DB, what i am trying to do is populate a table with a list of table names that the front end links to and their paths once a user has selected the data file location.
Instead of me having to force a user to place the DB files in a location of my choice when the database first loads it will prompt the user to select the location of the data file then (this is why i need the above) populate a table with a list of table names and their source, then establish a link to them for the front end to work.
Also if i ever send amended DB files or they move the location of the data files they can automatically re-establish the linked tables.
Hope this makes sense.
Cheers guys.
View 3 Replies
View Related
May 11, 2005
Please note that I am self taught (90% of what I have learnt has been off these boards!). I did make this form with info I found on this forum.
I am having problems with a log in box for a database, the line of code is -
If Me.txtpassword.Value = DLookup("password", "customers", "[customerID] =" & Me.EbayName.Value) Then
This code is in a Form; "password" is the value in the table of "customers"; [customerID] is the primary key value I wish to remember; Me.EbayName.Value I assume is the value in the table that access is looking for.
Questions -
A) It is not looking up the value for [customerID] - what have I done wrong?
B) In the line of Me.EbayName.Value what does the 'Me.' part tell Access to do? I assume it's a pointer to it's own form? What part of the line do I need to change to make it point to another form (or table)?
C) When this is finished how do I get the program to remember the 'customerID' while the customer is fillling out other forms?
James
View 2 Replies
View Related
Aug 8, 2005
I have an inventory table and a repair table for computer equipment. The inventory table is already set and working with all the data I need.
In the repair table, I would like to just enter the ID number and have some of the information already stored in the inventory table automatically entered into the repair table as needed.
For example, computer 138 has repairs done to it. I would like to then go into the repair table to add a new entry, type in 138 and have the some of the data from inventory table (model, serial number, etc) transfer into the repair table.
Is this possible with Access?
I have only been able to do something with the lookup wizard, but that doesnt seem to work well.
Can anyone point me in the right direction?
View 1 Replies
View Related
Sep 19, 2004
I have managed to get one combo box on a form (Categories) to look up the approprate values for that category and populate another combo box, i.e. if I choose Premesis costs in the categories combo box, I am given the relevant choices in the Details combo box, eg. rent, cleaning...
Now this is all great but I want the values I select in BOTH boxes to be entered into the underlying table. I have tried putting the relevant field as the control source, but the way it is set up means that only numbers are put into the table. Here is the code and stuff (I got this from a help site, so I have changed my table and control name to theirs to make life a bit easier):
cboStore (i.e. the Categories)
Row Source: SELECT tblStore.lngStoreID, tblStore.strStoreName FROM tblStore;
Event - AfterUpdate:
Private Sub cboStore_AfterUpdate()
Dim sManagerSource As String
sManagerSource = "SELECT [tblManager].[lngManagerID], [tblManager].[lngStoreID], [tblManager].[strManagerName] " & _
"FROM tblManager " & _
"WHERE [lngStoreID] = " & Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery
End Sub
Private Sub Label5_Click()
DoCmd.OpenQuery "qryCategories", , acReadOnly
End Sub
cboManager (i.e. Details)
Row Source: SELECT tblManager.lngManagerID, tblManager.lngStoreID, tblManager.strManagerName FROM tblManager;
The Tables:
tblStore: Field names: ingStoreID (Autonumber); strStoreName (text)
tblManager: Field Names: ingManagerID (Autonumber); ingStoreID (Number); strManagerName (text)
The Query (very simple):
qryCategories: strManagerName From tblManager; strStoreName from tblStore.
Phew! Is that enough info for someone to help me? I wouldn't mind even the numbers being in the table if there was some way that I could change them back to text for a report.
I'd be really greatful if someone out there could help - be gentle with me though, as I'm note very good at this code thing!
View 2 Replies
View Related
Dec 14, 2004
I've got a huge table with one column that shows a lot of duplicate
info in it. How can I let it identify the unique names and "hide" all the
duplicate info that falls under it e.g.
Columns:
Col.1:Fruit Col.2 Col.3
Apples A 1
Pears B 2
Peach C 3
Apples D 4
Apples
Apples
Pears
Pears
Pears
I would like the table only to show the say Fruit column with Apple, Pears,Peach
and list when you click on it all the related data to it. Along with this I would like to
associate all this info in the form. If I select Fruit in the form the related data should appear
below in maybe a subtable or something which I should be able to select from which info I need.
View 4 Replies
View Related
Mar 27, 2008
I know there should be a simple solution to this problem but i have not worked on access for some time and the cobwebs is too thick.
I have 2 tables one being Stock codes with related fields: Stock code id; Stock code;length;width;height.
Second table is Order Details with related fields: ID; Order id; Stock code;quantity;length;width;;height.
Did a query running the stock codes with length width and height.
Did a form with all details, calling up the stock codewith the query. The info on length width and height to the related stock code is on the form but how do i relate it back to the table Order details.
In the order details table the stock code is showing but none of the other related info.
As i said it is something small but i cant get round it.
View 3 Replies
View Related
Jan 15, 2006
I have a table that lists all of my available seating for different events.
My table columns look like this:
EventID - Section - Row - Seat - Available
Sample data in columns:
1 - A - A - 1 - Y
1 - A - A - 2 - Y
1 - B - A - 1 - N
1 - B - A - 2 - Y
2 - A - A - 1 - Y
2 - A - A - 2 - Y
2 - B - A - 1 - Y
2 - B - A - 2 - Y
EventID actually goes from 1-6
Section actually goes from A-F
Row actually goes from A-J
Seat actually goes from 1-36
When the user selects an event (or events) in the first box - I would like the query to eliminate the available seating, from the other events, in the results.
When the user selects a section (or sections) - I would like the query to further eliminate the available seating, from sections not chosen, in the results.
When the user selects a row (or rows) - I would like the query to further eliminate the available seating, from rows not chosen, in the results. And finally display whatever available seating is available - based on all three of the above criteria.
I purchased every access book I could find.. and I am slowly learning this. I'm a complete novice..
I'm not looking for any of you to do the work for me.. I'm more looking for guidence. I'm still getting to know all of the controls and their limitations. I have not yet written any VBA code. (It's still very confusing) I'm trying to get by on using the toolbox controls, using queries, macros and form controls.
View 2 Replies
View Related
Dec 7, 2006
Hi there :)
I'm not sure if I've picked the right forum topic for this... It's not a straightforward table question, and I think the only way I can do what I need is with a query of some sort. Apologies if it should have gone in 'Tables'...
The situation is: I'm creating a database (using Access 2003) in which our secretaries can enter exam marks for students. The difficulty is that there are ten papers (exams), each student's exam is marked by at least two examiners and there are at least 6 examiners per paper. Every examiner also marks more than one paper, but doesn't mark all papers.
So, I've got a table with all of the examiners and the papers they're marking and I've got a table with all the students and the papers they're taking. Because we have to enter the marks in such a way that we know both which mark an examiner gave and (obviously) which mark a student received (e.g. so we can look at statistics for each examiner), I thought the best way to do this would be to run a query that created a new table per paper in which the field names would be: Student_ID, <name of examiner 1 for the paper>, <name of examiner 2 for the paper>, <name of examiner 3 for the paper>, etc until the end of the list of examiners for that paper. The student's number would populate the Student_ID field and then the secretaries would enter each examiner's mark in their named field. Like this:
| Student_ID | A.N. Other | J. Blogs | J.Q. Public | Agreed_Mark |
1234A 72 74 73
2345B 65 68 67
3456C 71 73 72
4567D 52 51 52
Listing the name of every examiner (including those who aren't marking that particular paper) isn't an option (and would have to be hard coded into a table in any case, as far as I'm aware, either in a query or in a table design. This is something I'm loathe to do). Ideally, the secretary would be able to press a button on a form and have all the mark entry tables for all the papers created automagically.
The examiners for the different papers change every year, and not every academic marks exams every year, so this data will not remain static. Hard coding a table with examiner names would make more work for the secretaries. (I want to avoid this as all mark entering for all students is done in a single day. This means over 600 students at 3 marks per student [mark 1, mark 2, and final agreed mark] with time to contact examiners with questions... and only 2 secretaries, each of whom deals with a different section of students).
Because of the nature of the marking, it would also make quite a lot of work for the secretaries if we had to cross reference a generic Examiner_1 field with a different examiner for each paper, so the field name really must be the examiner's actual name.
Can anyone help me? I'm happy to do it with either VB or SQL; I'm not a programmer, but I can usually work out what code does even if I can't write it myself. I should be able to adapt anything presented as long as it's complete and doesn't assume that I know the basics. :) I've also got two reference books to hand: _Microsoft Access 2003 Forms, Reports and Queries_ by Paul McFedries and _How to Do Everything with Microsoft Office Access 2003_ by Virginia Anderson.
Thanks very much for your time. :)
-Jen
View 10 Replies
View Related
Sep 30, 2013
I split my db to front-end and back-end. 20 users are using the database. But now I have the problem: In my admin front-end db I created a new table and some reports to the table, but this new table I have only in my front-end db and it doesnot appeares in back-end db - so that other users has no data in hte report for the new table. How I can create new table to be in back-and table too?
View 5 Replies
View Related
Dec 25, 2005
I have a report in my DB that displays the results of several queries. How can I have the information displayed on my report entered into a table as a record when the report is generated?
Hope you can help!!
View 3 Replies
View Related
Apr 27, 2007
This may be a bit detailed, so stick with me. Is there a way to have a form which you can pull tabel information into, but look at a specific line? I am trying to create a database that will pull up the first line of a specific tabel's information, then when a command button is pressed, it will pull up a seperate form with the information from line 2 of that SAME tabel. I don't want this to be done by clicking the arrows at the bottom of the form. I actually want this to populate seperately.
Lets say I have a Loc_ID box that is pulling the Loc_ID from a tabel.
The tabel I am trying to pull from has an autonumber format and is sorted in that manor from a-z. Is there a certain type of logic that I should use however? As of now, each time I have a form come up, it starts at the first record, which is not what I want it to do. I want it to come up with the first record, then click a few buttons, then the next time a new form comes up with the same info on it, I want it to show the second record's information.
Any information anyone might have will be MOST helpfull! :D
View 3 Replies
View Related
May 17, 2007
Is it possible to construct a query that uses information from one table to run a series of Like OR Like... criteria?
I have created a table that contains partial part numbers and would like to query a second table for all the part numbers that contain these partial part numbers.
Meaning, BACB30DX6 would return the desired information for BACB30DX6-7, BACB30DX6-8 and so on.
The TBL_PARTS_FAM_DIA table contains approx. 200 items so 200 LIKE statements can't be the way to go... I'm hoping.
I tried: LIKE "*[TBL_PARTS_FAM_DIA]![PARTS_FAM_DIA] *" thinking that perhaps this would go through all 200 items... but of course that was wishful thinking.
Do I really need 200 LIKE statements or is there some easier way to do this.
Thanks in advance!
View 2 Replies
View Related
Nov 18, 2007
hi, i've been trying to find a solution to this problem but i can't seem to get my head around it. i have searched through the forums but found all the threads to be confusing although it probably is simple.
im a doing a project for a2 for a trailer tent company
got 2 tables where the problem lies in
order
stock
they only sell one trailer tent to a customer
when a trailer tent is sold, i want to update no in stock in stock table
i.e. trailer tent sold --> "no in stock"field for that particular trailer tent goes down by one. I would like the user to be able to change stock level any time as well so don't want it to be fully automated
sorry if i'm not making it clear, not really sure how to explain it
thanks for help, much appreciated
View 3 Replies
View Related
Dec 25, 2005
I have a report in my DB that displays the results of several queries. How can I have the information displayed on my report entered into a table as a record when the report is generated?
Hope you can help!!
View 1 Replies
View Related
May 9, 2006
Hi I have a field on my form called cost of order
I also have a field called deposit on the form that is generated by dividing the cost by 2.
is there a simple way (most probably) to the send this data to my payment table, I already have a deposit filed set up in the payment table I just want to post the data in from the form?
View 5 Replies
View Related
