Query, Forms,tables.
Mar 24, 2006
I have 5 forms and 5 tables.
The database is blank, but the information entered into the forms are saved into the tables.
Now I want to make a query so I can see some statistics regarding the information entered into the forms and stored in the tables.
I have created a query, but Im not sure what to do next, do I connect it to the query and it should give me desired output or do I connect the form to the table
or how does it work ?
need some feedback Im a little bit lost in this :(
regards
Frodo
View Replies
ADVERTISEMENT
Jul 23, 2015
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
View 14 Replies
View Related
Sep 5, 2004
I want to run a query that access a table behind a form. I would like the query to accept data that the user has typed into a text box within the form. What code do I use for the query to access this text in the text box? I want the form to then display the results of the query in a List Box. Also what code will I need to change data in a table by using a form?
Thanks in Advance
View 1 Replies
View Related
Apr 30, 2015
I have two tables (1- User) , (2-Dept) have filed name D_Sum and link ID of [User] to UserID of [Dept] table
My question is how to add both of tables and get all user with filed name D_Sum form [Dept] table
in order to filter User with that have dept or not
I try to do it but I get only user with dept ,, I need all user ...
View 6 Replies
View Related
Dec 6, 2005
I have a form which is based on a select query that brings together 2 related (one to many)tables. When I keyin data to the form it does not update the corresponing table.
Is it possible for a table to be updated in such a way, or do I have to create a form based solely on the table(s) without using a query?
View 2 Replies
View Related
Jun 24, 2014
I am running access 2007 in Win7. I have a time sheet application that I wrote many years ago. Within same I have a button to press to open a new form to enter time sheet data. The data for the form comes from a query that links Employee and the Timesheet history data file.In an effort to upgrade and make the application more portable and available to multiple users, I decided to migrate the data tables to a Microsoft SQL 2012 database. Various Access reports based on the SQL database run ok. But if I try to open a form to add a new record I get the following error message:-
"Run-Time Error '2105' You can't go to the specified record."
If I debug the error it shows:-
Private Sub
Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
End Sub
In an effort to make sure the original time sheet form load worked ok on the original tables, I changed the query to feed off tables in the local Access program and have no issues. It works perfectly. Also if I open the Timesheet Query that feeds the form I can add new records.However if I change the Query to look at the same tables on the SQL database I cannot add records. I can search all records from first to last but not add a record into the query. The add record arrow is greyed out on the query results.
View 5 Replies
View Related
Feb 11, 2015
I have 3 tables:
tblMain
BalanceID PK
GasID FK
ElecID FK
tblGas
GasID FK? not sure
BalanceID
tblElectric
ElecID FK? not sure
BalanceID
I created 1 query from all 3 tables, then created the form from that 1 query.Now when I enter data into the form and click to save it , it produces this error message:The Microsoft Database Engine cannot find a record in the tblGas with key matching fields 'tblMain_GasID.The tblGas does contain a field GasID, but does it mean that the field should be tblMain_GasID
View 12 Replies
View Related
Apr 19, 2007
I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
Thanks,
Josée
View 1 Replies
View Related
Sep 3, 2014
I am using Access 2010.I have a table that I am using to pull my data from other tables and a query.My table is called tblMyData.One of the field names is level1. This field points to another table, and gives the user the choices for states (examples California, Texas, Maine).
Another field name is level2. This field points to another table and give the user the choice for type of customer (examples Business, Consumer)
The field name level3 points to a query. The query, qryFinalChoice matches up the choices based on level1 and level2.for example, the user can pick California for level1, business in level2 and California Widgets or Los Angeles Clothing store in level3 (plus about 20 other choices).
If the user picks Texas for level1, business in level2, business in level2 and Houston rugs, or Texas style restaurant in level3 (or about 15 other choices).
-I am recording 1 for California on level1, 2 for Texas and 3 for Maine in level1.
-I am recording 1 for business and 2 for consumer on level2.
The query qryFinalChoice has all the combinations for state, business or consumer, and lastly business name or consumer name.
-qryFinalChoice has line1 to match up the choices for level1 in my table.
-qryFinalChoice has line2 to match up the choices for level2 in my table.
-qryFinalChoice has line3 to match up the choices for level3 in my table.
I do not want any of the Texas business names appearing when the user picks California, or vice versa.
My SQL in my tblMyData tab for level3 looks like this:
Select line1 from qryFinalChoice where line1=1;
I am able to get all the line items where California is a selection.How do I change my SQL to pull all the line1 choices where I have selected from level1, and all the line2 choices where I have selected from level2 automatically based on my pulldowns?
View 1 Replies
View Related
Dec 15, 2014
I'm looking into storing query data in temp tables for my reports run better. From what I'm reading, it seems best to have the temp tables in a separate db, and to break the links to avoid bloating of the FE database. I'm unsure how to do this with VBA, especially since my temp database will be password protected. When do I break the link - when I close the FE database?
View 14 Replies
View Related
Sep 21, 2012
I am using collect data via email process to collect data by email, purpose is to update not to add new records but to update. The condition for update is to have data from one table only.
I have 4 tables data from which should be sent by email. These tables are related. I made a query based on tables and query is update-able. When I use this query, wizard does not give option of updating the data but only of adding new records.
I tried to first make a make table from a make table query but that too have the same result.
Any ways to make treat these tables in a single table? Almost all fields except one shall be just to read and one field shall be updated.
View 5 Replies
View Related
May 27, 2013
I am a girl guide leader trying to make a faster way to keep track of my unit's completed badge and program work. I have made tables for every area I need (I started with one table but it was too large to change into a form).
In total I have:
- Basic Member table (main information like their name and how many years they have been in, whether they are still in the unit, etc)
- Program area tables
- Interest badge tables
What I want to do is be able to "add/edit" members from the main form, then go to all the sub forms for their program and badge work -- problem is, how to link them!
I am using Access 2007-2010.
View 2 Replies
View Related
Dec 12, 2013
I'm developing a system which uses linked tables (to an Oracle database) and subforms. I'm trying to add functionality to create new child records, but am running into errors when saving these records.
Parent table is PATIENT, child is DIAG. I have my DIAG Form_Current event set up to detect whether a record is new. When this happens I populate the DIAG foreign key with the relevant PATIENT primary key, and run a query to the underlying database to find out the next DIAG primary key. I then populate all the necessary fields.
On attempting to leave this record however, I get an ODBC call failed error, telling me that the the primary key constraint in the database has been violated. The reason is that Access isn't saving the record to the underlying database. I've tried to prompt this using Me.Dirty and RunCommand acCmdSaveRecord, but both of these just generate type mismatch errors.
What am I missing? Is there some particular set up for linked tables and creating new sub-records that I need to put in place?
View 1 Replies
View Related
Apr 25, 2013
I have created a database in which I have used a combo box on several forms relating to corresponding tables. These combo boxes all reference the same table with names (text fields) listed. I am having difficulties getting the data to feed into the table from the combo box using the form. Only the ID numbers appear in the table.
View 3 Replies
View Related
Jul 29, 2013
I have one database that I am working on and within it there are several Tables. 2 of the tables have similar information on various organisations and I need to query these 2 tables to get a simple mailing list of ALL organisation addresses and the main incumbent there.
Within Access (2007) and in the SQL View window, I have made a simple query to give me a list of all the organisations and incumbents but cannot get it to work correctly. Is there an easy way to get this information out of the 2 tables? I realise that the SQL in SQL View has to be formatted differently. I have also tried the Microsoft Query Wizard but that doesn't work either as it gives in a side by side list which is useless to me.
The Field names that are required are as follows:
<Incumbent>, <Organisation>, <Address1>, <Address2>, <Address3>, <County>, <Postcode>,
View 8 Replies
View Related
May 4, 2015
I have 2 forms, Form1 is used to enter staff personal and salary information while Form2 will be used to enter Bank information. I am using three tables to maintain this information:
1- Personal Info
2- Salary
3- Bank Details
All tables are joined in relationship by using a field StaffID which have to be entered manually as I can not use auto number. I have enabled cascade update and delete in relationships
The problem is if i use the Form1 to enter the personal & salary information, the StaffID which I enter in Form1 is automatically updated in salary table as Form1 is using both personal & Salary tables. But the StaffID is not updated in table Bank Details. This is crucial as this StaffID is a link between Personal and Bank table. Is there any way that when I enter StaffID in Form1 it will automatically append StaffID in Bank Detail table? Or can I use any query to append StaffID to Bank Details table.
View 2 Replies
View Related
Nov 29, 2007
Can a Append Query move all my data stored in multiple tables to another database with a identical table structure?
Because as I try to work the query, I keep getting prompted to "Select a table" I want to append to, and I don't want to append to just one table...
View 4 Replies
View Related
Jun 22, 2007
hi. I have two monitors. on the left I have a table with 120 000 entries. On the right I have a form, that searches the entries and shows me the data from the fields in textboxes. Each Time i search (the table is in alphabetical order) in the form, I have to check the table to see whether the company is there twice - because the form does not tell me that. for instance, there may be an entry for american express and another for american express PLC - same companies. Is there a way that when i click search, the table jumps to the same place that the form just jumped to?
Thanks
A;ex
View 4 Replies
View Related
Aug 16, 2006
I have 3 tables Metrics, FixedExpenses, Variable Expenses, I want them all to have a date in a form, and a subform for all the records to be added. I want to be able to do this without having 3 seperate date tables linked to the other three tables. I am not sure how to do this correctly. (I want to be able to see all the records for a certain day in these forms.)
View 11 Replies
View Related
Feb 13, 2007
I was wondering I am using
5 tables to hold information about people fly fishing in rivers.
tables are
Table 1. Name of the person
Table 2. Date,place,number
Table 3. Salmon.trout.snag.spawn
Table 4. Bait and weather condition
I use forms to enter information into the tables, which work good.
But when I take a look at entered information in the tables, I cant see that the person that entered hes/here name first what data belongs to that person from the other tables, its like ther link between the data in the tables is missing or something like that.
I was wondering how to fix this, and what it is called.
Would be great to get some feedback takes me to long to search for it in the access book.
regards
Frodo from Iceland
View 2 Replies
View Related
May 8, 2007
Hi, somewhat new to the design of databases. Here is what I want to do...
I have a table with 3 fields called tbl_WE: Credit#, CreditName, & CreditReq.
Ex:
Credit#: WEc1
CreditName: H2O Landscape
CreditReq: Reduce water reuse
Credit#: WEc2
CreditName: H2O No Potable Irrigation
CreditReq: Non-pot water irrig
In my form, I use a combo box that once the Credit# is chosen, the CreditName & CreditReq autofil....but when I try and introduce another set of Credit's from another table, it replaces my original data of the WE credits:
Credit#: SSc1
CreditName: Site Selection
CreditReq: No farmland, floodplain
My question is this: If I eventually want 6 forms (one for each Credit type), do I have to have 6 different tables?
How do I tie tables together if the only info that is changing is the Credit#, CreditName, & CreditREq?
Thanks in advance,
Katie
View 1 Replies
View Related
Feb 22, 2005
hello
I have a form which has the following fields
name, ref,admission date, opening balance, closing date, closing balance, average balance and interest calcualtion (I need to be able to claculate interest using weekly interest figures on an average balance)
I want the form to have a drop down menu on the name which is linked to table 1. when a name is picked the ref, opening balance and admission date will aotuomatically be completed (from tablwe 1) I want to then type in the closing date and closing balnace, and for the form (underlying query) to automatically calcualte the average balance and interest figure. This infomation then needs to be saved in a separate table (table 2) when the form is closed.
whats the best way of doing this? mostly having problems of taking info from one table and then saving it in the second.
cheers!!!
Mojave boy
View 1 Replies
View Related
Feb 25, 2005
I am new at this. I have a database table emp with a check box for "Exempt" employees. I have a form "Enter time card" that is a data entry and would like to automatically populate the overtime field with the value of regtime*1.5 if the check box is ticked. I am having trouble doing this. Please help!! :confused:
View 4 Replies
View Related
Dec 16, 2005
Hi All,
Did a quick search but couldnt find anything....
Bascially I want to create a form from multiple tables....
How easy is this to do?
Thanks
View 6 Replies
View Related
Jan 30, 2006
Hey guys, bit of beginner so bear with me, i have a for and im trying to use two table, i want a two buttons on the form, one for printing one table the other for printing the other table, also i was wondering if it is possible to add a search button or something similar, that would search oth tables??
Cheers guys
View 1 Replies
View Related
Jul 14, 2006
just a bit of back ground.
my project is to create a database for a warranty dept.
idealy they would like to create a form that allows the basic details
return number,
date,
customer
etc.
put they want to be able to list all the items they get returns.
for instance, a customer may send back multiple products in one box.
at the moment i have a subform set as a table so they can input the product, manufacture etc. but it only lets me do one line of details
View 8 Replies
View Related
