2 Tables + 1 Query = 1 Headache
Jul 28, 2006
I am trying build what I thought was a super simple data base, but this is driving me crazy.
I have a group of building maintenance manuals (12) each of which is subdivided into sections. I have created a list of all the section headings and respective page numbers and have imported them into a table (tblMASTER ) with these fields:
MasterIndexpkf
SectionNumber (unique sequential number 1-311)
SectionName
PageNumber
I have a second table (tblCROSSREF) with these fields:
CrossRefIndexpkf
MasterIndexfkf
SectionNumber
SectionName
PageNumber
The two tables are linked in a one-to-many relationship on the respective MasterIndex fields.
I also have a query (qryMaster) which includes ALL the fields in tblMaster which serves as a lookup to the SectionName field in tblCROSSREF.
What I want to do is to select a section number in tblMaster and enter all the possible cross references in tblCROSSREF by selecting the available section names from qryMaster.
This seems to me to be sooo simple, but I am having a real headache!
My primary problem is that when I use the lookup function in the SectionName field (in tblCROSSREF) it returns not the Section Name, but the Primary Key Field number.
This thing is making me feel like the AFLAC duck. Can anyone give me a clue about where I have gone wrong?
Thanks,
Rick
View Replies
ADVERTISEMENT
Mar 20, 2007
Ok, this is what I want to do :
I want to 'append' individual records from 2 tables and place in an archive table or within another database, whichever is the best option.
I then want to be able to 'delete' the relevant records from one table.
The tables are tproperty and trents. This property paid rents but has since been sold. Therefore it no longer belongs in the database, however client wishes to keep details of the property/person/and rents paid in past, for any future ref. These tables are linked in relationships to tlessee and tbilling.
I've read books/notes/looked on here for inspiration and the right direction! If I choose 'append' which seems pretty straight forward then a 'delete' query, how do I choose only one record? Is a make-table onto a different database a better option? It would appear that the whole table is copied over? Can't understand the issue about auto-numbers being copied over? Do I have to use an append/delete query for each individual property that's ever removed?
I'm at a loss! :) Thanks
View 1 Replies
View Related
Dec 4, 2007
Hi all,
ok i have a bit of a problem here and this is it.
I have set up a quesry based on 2 forms 1 orders and 2 order details.
i have the set a criteria on the report to print a order number
i have then set up a report based on the query.
Then i have created a command button on my form up untill here it is fine the problem is when i create the order and send it to print the criterai box pops up which is ok but because i am using a touch screen it will not be viable for the user to enter the number each time he prints i would want it to pick it up from the current order id.
Is there a way to do this as i am sure all invoice systems and everything should be working like this or is there a better way to do this or am i doing something wrong i have attached a screen shot of what si going on
Thanks
View 6 Replies
View Related
Jun 1, 2005
Hi guys
I'm having issues with getting UK dates to show in access.
I created a table and then undertook an insert with the following:
docmd.RunSQL "INSERT into tbl_test([dates]) VALUES (#01/06/2005#);"
Note that my machine is in UK local and the format of the field is short date. The date in access is 06/01/2005. ie 6th Jan , not 1st June as it should be
If I set up a query via the query wizard to look at this field then I see that the date is shown in US format.
I tried to use the following in the field section:
result: format(dates, "dd/mm/yyyy")
and the date still showed up at US ie 06/01/2005. argghhhh. (assume due to the fact that format returns a string and I'm looking at a date field)
Any ideas how to overcome?
View 3 Replies
View Related
Sep 28, 2006
I have a main form [mainform] with several subforms on it, two of which are on a tab.
On the second tab [page79] there is a subform [subformlist] which has like 25 checkboxes on it, any combonation of which can be picked by the user.
I need there to be at least one pick required of this subform, else the record won't show in a general query I have, since the table the subform is based on is linked to the record.
Is there anyway to make making a choice of one of at least one of these checkboxes checked a manditory thing before the user can close or move on to the next record?
I've tried making an "Other" box that on the Table has a default value of "True" which I thought would cause it to show in the query record...to no avail.
The only true way to make this work is to have it mandatory that the user pick from AT LEAST one, usually more before the record is complete.
Any help? Other than writing a MASSIVE If...Then statement...?
Or is there a way to make it so the choice isn't required for the record to show up in the query?
I'd post...but my db is too massive, even zipped.
:(
View 2 Replies
View Related
Feb 7, 2006
I've been searching in vain for a solution to a relatively simple bit of mathematics that I’d like to Access to undertake.
It involves interpolating between data, as illustrated in the following example. Basically have several items, and let's say different "prices" dependent on the number of units purchased. The relationship between the “prices” is not a straightforward formula, therefore I need a query to return an interpolated price for a given number of "units".
DATA
ItemID Price10Price20Price35Price50Price100Price 1000
1£70£135£200£315£800£6500
2£23£31£61£89£210£1500
OUTPUT
ItemIDUnitsPrice(int)
117?
231?
So, for example
if Units <10, Then Price(int) = Price10
if Units = 17, Price(int) = Price10 + (17-10)*(Price20-Price10)/(20/10)
if Units = 32, Price(int) = Price20 + (32-20)*(Price35-Price20)/(35/20)
if Units >1000, Then Price(int) = Price1000
I'm aware that IIF statements might do the job, but I have 15 different 'Price' points, and don't particularly want to nest them all.
This problem was solved previously in Excel using a clumsy set of VLOOKUP/HLOOKUP functions in conjunction with If statements.
View 2 Replies
View Related
Oct 19, 2006
Hi all,
I got a headache to changing my form's filename... i am totally a newbie in Access while in the few months ago... so i just create all those form or table name's for the way i like. But now i had already know how important of management of the filename because when u r using the module to write the code u will be headache to look for the filename that u want in the code. So that is important to add 'tbl', 'frm', 'Qry' infront of the filename.
So do anyone have a good way to edit all the file name with all match to the other in the module with a quick way rather then one by one editing (really tired for one by one) Thanks!!!
View 4 Replies
View Related
Dec 16, 2006
I need some help. My brain just can't grasp how to do this.
I have an Access DB. I sell vinyl records. I have an order form. When a customer orders a record, each vinyl record has a unique ID#. No two vinyl records are alike. I have four tables: Customers, Orders, Inventory, & Order Details. I have an "Order Form" form where I input all the info. It's an all-in-one form where you can view and data entry in one screen. The form has two subforms in it.
Here's the problem:
If a customer orders more than one item (i.e. a vinyl record), each Inventory_ID (representing one vinyl record) is associated with one Order_ID which is, in turn, related to one Customer_ID. If I have one Order_ID that has multiple Inventory_IDs (i.e. has ordered two or more vinyl records) that belongs to one customer, how can I calculate that total cost?
Currently, I have a calculated field within my all-in-one form that calculates only the price of the Inventory_ID that one currently sees on the subform. The Inventory_ID item has a set price, for instance $5.00. If I scroll through the ordered Inventory_IDs, the calculated field will only display the price of the product that you currently see. It's not really calculating anything, I guess. It's just bound to that field, but I need to sum up all the price fields for all the Inventory_IDs that are associated with one Order_ID & Customer_ID whether it's a query or calculated field...I just need it done so that it'll display on this form.
Any help would be much appreciated. I can show you whatever you need to see. Thanks in advance.
David
View 10 Replies
View Related
Jul 26, 2005
Ok, here's something I think I've done dozens of times, successfully. Now I can't get it to work.
I have a Parent Form with three Subforms (based on 3 separate, linked, tables).
I have a combo box on the parent form which looks up a piece of EQUIPMENT by its unique ID and the other two forms display equipment DETAIL and SERVICE HISTORY related to that equipment. It works just great until I use the ParentForm's navigation buttons. The two Subforms change to display the information relevant to other pieces of equipment, but the combo box continues to show the original look up.
Whaaaat have I done or not done here?
Thanks,
Rick
View 2 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
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
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
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
Oct 12, 2005
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,
View 1 Replies
View Related
Oct 26, 2007
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 Related
May 10, 2005
I 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
View 5 Replies
View Related
Nov 16, 2005
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!
View 1 Replies
View Related
Dec 1, 2005
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
View 8 Replies
View Related
Dec 18, 2005
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?
View 1 Replies
View Related
Apr 19, 2007
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
View 2 Replies
View Related
Mar 5, 2008
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
View 1 Replies
View Related
Feb 15, 2005
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 Related
Dec 28, 2006
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?!
View 1 Replies
View Related
