I have four tables....that do no require relationships.
The four tables have different fields, but have one field in common, the Name.
I want to create a query with all the Names from the tables.
How?!
I have a table (Working_Year) that is storing a single field which is the year (current_year) that is selected by the user in my initial form when the database is loaded. Am I able to use that data in other forms and reports with out linking the table? I would like to simply display the current_year the user is working in a textbox somewhere on the forms (example form named: "Main Entry") and use the current_year as a filter on reports (example report called "Gifts - by Type").
View 1 Replies View RelatedHow to calculate all records that have not yet been linked to other records. For example: I have 1 Product, which is linked with multiple components which is subsequently linked to subcomponents. All these types can be linked to Alternate Parts.
In order to reduce redundant information I would like to show the unlinked parts to the user. Therefore I would like to make a query which shows all unlinked parts.
I have the following tables with subsequent PK Part Number field values:
tblProduct->ProductPN
tblComponent->ComponentPN
tblSubComponent->SubComponentPN
tblAltParts->AltPartPN
I am unable to find a appropriate query structure with criteria for this issue due to the many different fields required and the fact that if in one query all these tables are shown no data will be shown ...
I am using a Table to store Parameter Values for a Split Multiuser DB. Question I have...Can I have this Table on the Front End while all the others are on th back end? The use for the table is to store parameter values for multiple queries without the user having to reenter the parameters.
I've tested this and it seems to work...but would like to know if there may be complications.
FYI. I am a Novice VBA user.
Thanks,
Felix
Hi, How don't know how stupid my question is but i was wondering if it was possible to insert a form inside an other form without linking them???
The reason for this is that the 'inserted' form would work as a 'search' form and could display usefull information for the main form to be filled even though there is not really any fields that can be linked together. Is anybody got suggestion for this matter? :confused:
I have an unlinked subform on a form. This unlinked subform contains the contents of table: employee. What I want to do is have a textbox that, when typed in, will auto filter out records that don't match what was typed so that once you have correctly typed the employee name all you are left with is records of employees with that name.
View 4 Replies View RelatedDear friend,
I have a question if anyone can help me out. I have as assignment to do for a small firm. Dataentry application primarily with few search and edit functions. This will be used by multi-user.
I am yet to decide whether to make the form and the database linked or unlinked. What I mean is:
1) I want to keep form and db in seperate mdb.
2) I have to decide if the form (in seperate PCs) should be linked to the db over network or should it be programmatically connected.
3) I have a feeling that if the tables are linked with the form (the tables that i see in the database window), then when I open the form the underlying records/table will gush into the client PC jamming the network.
4) If i programmatically connect the form with the tables in the network db based on query then probably only the required record will come through the network and fill my form not jamming the network.
Any idea, pls help.
Regards,
Prodigy.
I have a back-end db in access 2010, linked to a set of sharepoint tables on office365.
I want to make a completely unlinked copy of the db to store a "snapshot" of the data on a fixed date.
I have tried "save as" using the "Save as Local Database" option, but for some reason, it still saves as linked tables.
How to go about it?
I have three tables.
The first is my table of pupils which is linked to the second table: a list of awards and a score 1-5
My third table is the list of 10 awards.
My problem is that (in my report) I want to show all 10 awards for each pupil regardless of if they have achieved them. I'm struggling with the underlying query to always show all 10 awards.
So, little Johnny has achieved a score of 5 in two of the awards, however the report card will show blanks for the remaining awards on his report.
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
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?
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 RelatedI 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.
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>,
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.
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...
Hi to all, as you can see I'm brand new in access. I just need some advise on how you can probably do this:
I have a directory with tables for every year scince 1984 to 2004 where the structure is the same, so now I want to have a form where I can put the last name of somebody, so access can pull every table and tell me where did it find a similar result.
I hope you can help me,
Regards,
I want to create one query from two tables. When I run the query with two table the data sheet is blank. How do I combine two tables so when I run one query all records appear from both tables?
View 5 Replies View RelatedI have two tables with a one to many relationship but not every record has a match on the many side. When I run a query containing both tables and tell the relationship to use all records from the one side and only the records that match from the many side it returns correct execpt when the 1 side has a match only the matches display not the original record from the 1 side.
Table1
ID 1
ID 2
Table2
ID 2 SubID1
ID 2 SubID2
Query display wanted
ID 1
ID 2
ID 2 SubID 1
ID 2 SubID 2
My main result is a report.
Thanks
Hello again everyone:
I have a new problem! I have four dbf files imported as tables. Each table has the same fields. One of the Fields in the tables is Parts. What I want to find if possible is the following:
1. I want to see all unique parts in these tables. If table 1, 2, 3, & 4 all have part 1 I only want to see it one time. I guess what I am saying is I really only want to see each part one time even if it is in all 4 tables, 3 tables, 2 tables or 1 table. Can this be done? I hope you can understand this request and thanks for the help!
I have these five tables in a DB. I have a query for each table to append that table. I will run these queries the first of each month and have the tables show the counts. In this case I ran the queries on 11/29 and again on 12/1. I want to show the change in the counts from month to month.
CLIENTS DATE
10 11/01/2005
15 12/01/2005
STATES DATE
15 11/01/2005
15 12/01/2005
EFT DATE
12 11/01/2005
14 12/01/2005
NCP DATE
2 11/01/2005
5 12/01/2005
EMPLOYER DATE
15 11/01/2005
18 12/01/2005
Now I want to create a table query to combine the results from each table and date so that I may create a report from that table. The table should look something like this.
DATE CLIENTS STATES EFT NCP EMPLOYER
11/01/2005101512215
12/01/2005151514518
This is the query I’ve tried with no luck as this query comes up with 32 lines in the table when there should only be 2.
SELECT
[CLIENT ].DATE,
[CLIENT ].[CLIENTS],
[STATES].DATE,
[STATES].[STATES],
[EFT].DATE,
[EFT].[ EFT],
[ NCP].DATE,
[ NCP].[ NCPS],
[ EMPLOYER ].DATE,
[ EMPLOYER].[ EMPLOYER ]
FROM
[CLIENT], [STATES], [ EFT], [ NCP], [ EMPLOYER];
Can someone show me how to correct this query to get the results I’m looking for. Thanks
I'm working on a maintenance system and sometimes, machine operators fill in MS Access forms with Machine Number or Location No or BOTH whenever a machine needs repairs.
Now, whenever I generate a report, only those entries containing an Asset No comes out on the report.
What I would like to do is to generate a report that shows primarily the Asset No and in instances where the Asset No is not recorded down, the Location No shows instead.
How do I do this on the query table?
I want to add excel spreadsheets to the tables and then use a query to filter them out
Can I make one query and apply them to the different tables?
Thanks
I have a set of data in one table with another set of data in a seperate table, both tables have been linked so I have a MAX and MIN set of values -this works fine however I now wish to work out the difference ie;
MAX - MIN = ? but I now get an error as the data is in 2 different reference tables which are set by an external source so I have to use both tables linked by a common thread, I use the Date - any ideas how I can resolve this issue
Regards
Richard G
I have a query using 2 tables. However the second table is not always required and I notice that if the second table is not used then the info from just the one table is not included in the response. How can I rectify this problem?
View 1 Replies View RelatedHello all:
I have multiple tables in my access db, the structure of the tables (4 in all) are exactly the same except that the billing dates are for year 2001 to 2004 i.e. separate table for each of the years.
I don't want to copy the information in these 4 tables into 1 table because I have over 2 million rows in each table. However, I want to be able to run 1 query that will provide information from all 4 tables. E.g. filtering for billing dates between 2001 to 2004 for a particular customer.
How do I join the tables together so that I can run a query on all 4 tables at one time.
Thanks for your input.
Odun