make table query with related tables

I have an equipment maintenance database, I would like to put my inactive 
(gotten rid of) equipment in an inactive table, and not loose the 
relationship with the maintenance table. basically make two tables. I did a 
make table query, made my inactive equipment table. I could not delete the 
equipment from my first table due to key rules. I then made a relationship 
from my equipment table to my maintenance performed table. could not make a 
one to many and enforce integrity due to the first equipment table. I am 
going to delete the relationships, run a delete query on my inactive marked 
equiment in my equiment table, and see if I can make both equipment and 
inactive equipment one to many and enforce integrity. Do you think this is a 
good way. 
I want to be able to mark a piece of equipment as inactive and have a macro 
make it go from one table to the inactive table and not loose its maintenance 
records. 
Let me know if I am going at it correctly please.

0
Utf
1/23/2008 1:47:56 AM
access 16762 articles. 3 followers. Follow

2 Replies
755 Views

Similar Articles

[PageSpeed] 6

On Tue, 22 Jan 2008 17:47:56 -0800, Access G-man
<AccessGman@discussions.microsoft.com> wrote:

No I don't think this is a good way. Rather I would keep all equipment
in one table, and have an Active yes/no field.
In most queries you would only display the active equipment:
select * from Equipment
  where Active=True

Another technique I have used is to have a dropdown showing first the
active equipment, then the inactive:
select * from Equipment
  order by Active, EquipmentName
Your dropdown could have two columns: EquipmentName and Active.

A more elaborate technique uses a union query to first display Active,
then a separator line (perhaps containing text like 'please select
from above this line'), then the inactive items. Additional code can
ensure the user cannot choose from below the line.

Many options. It all depends how much effort you want to put into it,
and what would work best for your users.

-Tom.



>I have an equipment maintenance database, I would like to put my inactive 
>(gotten rid of) equipment in an inactive table, and not loose the 
>relationship with the maintenance table. basically make two tables. I did a 
>make table query, made my inactive equipment table. I could not delete the 
>equipment from my first table due to key rules. I then made a relationship 
>from my equipment table to my maintenance performed table. could not make a 
>one to many and enforce integrity due to the first equipment table. I am 
>going to delete the relationships, run a delete query on my inactive marked 
>equiment in my equiment table, and see if I can make both equipment and 
>inactive equipment one to many and enforce integrity. Do you think this is a 
>good way. 
>I want to be able to mark a piece of equipment as inactive and have a macro 
>make it go from one table to the inactive table and not loose its maintenance 
>records. 
>Let me know if I am going at it correctly please.
0
Tom
1/23/2008 4:34:16 AM
On Tue, 22 Jan 2008 17:47:56 -0800, Access G-man
<AccessGman@discussions.microsoft.com> wrote:

>I have an equipment maintenance database, I would like to put my inactive 
>(gotten rid of) equipment in an inactive table, and not loose the 
>relationship with the maintenance table. basically make two tables. I did a 
>make table query, made my inactive equipment table. I could not delete the 
>equipment from my first table due to key rules. I then made a relationship 
>from my equipment table to my maintenance performed table. could not make a 
>one to many and enforce integrity due to the first equipment table. I am 
>going to delete the relationships, run a delete query on my inactive marked 
>equiment in my equiment table, and see if I can make both equipment and 
>inactive equipment one to many and enforce integrity. Do you think this is a 
>good way. 
>I want to be able to mark a piece of equipment as inactive and have a macro 
>make it go from one table to the inactive table and not loose its maintenance 
>records. 
>Let me know if I am going at it correctly please.

You're making it much harder than it needs to be.

Add one Yes/No field, Active, to your equipment table. Set it to Yes/-1 for
all active equipment, No/0 for inactive equipment.

Base your forms and reports on a query selecting only active equipment.

If the field is indexed you'll never notice the performance difference (well,
if you have 15000 active records and 150000 inactive you might if you
concentrate...)

             John W. Vinson [MVP]
0
John
1/23/2008 5:50:11 AM
Reply:

Similar Artilces:

How can I make my signature as an hyperlink? Outloook 2002 SP3
-- The BOSS http://dts-l.org/goodpost.htm -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. "intelegend" <intelegend@discussions.microsoft.com> wrote: Absolutely nothing. ...

group query 02-05-10
I'm trying to create a query that gives me the average price of each product based on the last 10 purchases. The table is called "t_worksheet", with fields called "buy_price_alt_currency" which represents the price, "processing_grade" which represents the product and "collection_date" which represents the purchase date. I've been struggling with sub queries but can't get the results I need. Please could somebody help. Thanks Ian I think this will work (UNTESTED) -- qryLastTen -- SELECT processing_grade, buy...

Table with FIFO Balances
Can anyone tell me which table holds FIFO balances in it? Thanks, Jocelyn Jocelyn, IV00102 has the quantities but I suspect your looking for IV10200 which has the history and the current FIFO layers. More info here: http://msdynamicsgp.blogspot.com/2007/09/weekly-dynamic-inventory-value-via-sql.html Mark (DynamicAccounting.net) http://www.dynamicaccounting.net On Oct 1, 3:44 pm, Jocelyn <Joce...@discussions.microsoft.com> wrote: > Can anyone tell me which table holds FIFO balances in it? > > Thanks, > Jocelyn ...

very interesting query- please help
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Merge 2 tables with conditions
Hi! I have 2 tables: Table Users: ID Name Password Birth Table Status: ID Status Both tables are related by "ID" field, but both tables have not the same number of elements I want to obtain a new table with this structure: Table Result: ID Name Password Birth Status As I said before both tables have not the same number of elements (rows) so in my result table, "Status" field should be empty for those "ID"'s not found in "Status" table. Example: Users: 1 2 3 ...

Form Error (Query Update)
Hi, I have about 12 people to work on the database and each has own userID and password. Their UserID and Password input and the form link to the query that has the filter on "UserID and Password" for their input. The message said "Can't Update; Query Locked". Is there I do something wrong? I thought the form is the shared for everyone to input based by UserID and Password. Please help. Thanks We aren't there. We can't see how your database is set up. For instance, do you have a split database, with a single "back-end" file on your network an...

Database Query #3
I posted this earlier with no replies. Anyone got any ideas? I am using a database query to retrieve data from another database excel file. The problem I am having is that the column of data which I have contains records which have both text and number formats. When the data transfers, it only brings those records which are number format. Is there any way to make is so that the database query will pull both number and text formats, as I can't easily change it so that it is one or the other? Thanks The following article in Dick Kusleika's web log may help: http://www.di...

Transpose & offset data from a table
I have the following table and I would like to have each item on one row # Acc# Dr Cr # Count Info 1271 1030 4.67 1 3 Cr 2200 0.27 1 3 Dr 6050 4.41 1 3 Dr 1288 1030 7.87 2 2 Cr 6090 7.87 2 2 Dr 1617 1030 61.9 3 4 Cr 2200 2.59 3 4 Dr 6050 4.11 3 4 Dr 6550 55.2 3 4 Dr 1958 1000 45.9 4 5 Cr 1000 39.2 4 5 Cr 2200 4.01 4 5 Dr 6310 37.4 4 5 Dr 6630 43.7 4 5 Dr There is info that can help with determining items Count - checks how many items should be transposed Info - provides the information whea...

sum tables cells using row and column conditions
Hi, i need to sum the values in a table based on a name match in COLUMN B and a text match in ROW 4 - say for each occurrence of "Jim Smith" in range B7:B505,when "text" appears in range E4:GC4, SUM all cells which will contain number values- so if:- text1 text2 text3 text1 Jim Smith 3 6 2 4 Sue Brown 1 5 1 7 Mark Bosman 2 9 3 6 Jim Smith 5 4 2 3 the result would have ...

HELP!!! How do you create a query crieria for multi values??
Hello all, please hyelp, its an emergency!!! Need to create a query where one of the searches is male or female, how do I allow user to enter both as the search options?? Also how do I convert tix boxes into a query criteria (e.g. serach for all ticked) Thanks so much for your help ...

How can we make a control in a form to a required field
Hello, My form is based on a query which consists of 3 tables: 1. Inovices 2.Supplier 3.Projects There is a tendency save the record without filling in the supplier number and when they want to update the record they can not see anymore the record. Wht is the VBA in the controls/field of the form that supplier number and project number are compulsory meaning they can not save the record if they leave thses 2 fields missing Thanks in advance -- H. Frank Situmorang Frank, Don't go about this in VBA. Put the restrictions in the table design by setting the field properties to requi...

make a spreadsheet
Hi leslieguy More information please (in the body amd not in the subject) See http://www.cpearson.com/excel/newposte.htm -- Regards Ron de Bruin http://www.rondebruin.nl "leslieguy" <leslieguy@discussions.microsoft.com> wrote in message news:0299B4A9-C389-48C3-B284-0BD13C73C5AC@microsoft.com... > ...

Display Date from Unbound Field in Form in a Query
Just had an issue that in 10 years of using Access never saw - just wondering if I just never encountered it or what... I have a form running a query, the user enters a date. The report needs to show the date - so the query has a field like this: report date: [Forms!]![frm_report]![txtReportDate] I had a problem exporting to Excel - seems the direct placement in the query led to characters that Excel could not understand. I actually had another date field from the form that I did a date add on, it worked fine. I eventaully put the form reference in a Format, the issue was gone. The...

Data from Excel to Access Table
Hello, I have this code setup in Excel but I want to be able to upload multiple rows at one time. Is there a way to incorporate this into my code? Also, is there a way to upload cells that are blank as well? Right now it won't allow me to upload cells that are blank. Can this be done? Sub UploadData() Dim MyCn As ADODB.Connection Dim SQLStr As String Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _ "DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\BC Quality Action Database.mdb;SystemDB=\...

how to make gaps in plotted data when cell has formula
I have a simple data set with a value for each month except June. I want the chart to reflect a gap for June, not a zero value. Although I did tools, options, chart, make empty cells plot as gaps, it still plots the June value as a zero. In an attempt to get rid of div/o error, I have a formula in the data set as If (b5=0, "", c5/b5) . What I'm plotting is the quotient c/b. We have determined that the formula is what is causing the problem. Interestingly, if I open this same worksheet (as is) in quattro pro, the gap appears in the chart. Is there a different way to do th...

Handling bill-related dates
Hello (Using MS Money 2007) I would like to register the following date when managing bills: 1) The emission date 2) The due date 3) The payment date, which can be before or after the due date. Is it possible? Thanks a lot Pick one. "EMoscosoCam" <EMoscosoCam@discussions.microsoft.com> wrote in message news:68EE3D99-8372-4C9C-907B-80282DA47070@microsoft.com... > Is it possible? I would like to have all 3 dates registered, but I do not know if MS Money allows me to do so. Moreover, If a bill arrives, I would registered the emission date and the due date. But when...

What factors make Outlook slow
My computer at home runs Outlook (2002 I think). It was originally set up to access my POP account, and then I added my work's Exchange server to it, although it doesn't "dominate" Outlook. In other words in the Folder List pane, I have both my Personal Folders, and Exchange - although Exchange goes over IMAP I think. My computer at work has Outlook 2002 also, but it's set up so that Exchange is in charge. My Outlook at home has recently become phenomenally slow. Just clicking on a POP message that has already been downloaded and is in my inbox results in a 2-3 second...

Excel Query Opens Source
All, I am one of several offices supported by a purchasing group. This purchasing group maintains the status of all purchases in an Excel workbook. They will not open up the workbook for all. I have convinced them to let me create a workbook that queries theirs and returns just my offices purchases. They will have given everyone access required so that the query will work. As long as the exact location is not readily available and all of the data is not available, they are happy. Everything worked great for two weeks, but a week ago when the data is refreshed, the entire source workbook is o...

customer DB email address query
I would like to run a query or chrystal report to pull off all my customers email addresses so I can send one single email to all of them. Does anyone know the best way to do this? Thanks! I have a report for HQ Mgr that will generate a listing of all of your customers within RMS that have an email address assigned. You would use this report (filter so email <> (blank)) then generate and export. Do you have an address where I can send it? -- Jocelyn "zoostation" wrote: > I would like to run a query or chrystal report to pull off all my customers > email addr...

External Data Query
I have a worksheet that was originally set up with the Get External Data-->New Database Query menu option, I used ODBC data sources and entered my user id and password at the time. Since then, I use the Refresh Data menu option monthly to get new data. The password is embedded in the worksheet so I don't need to enter it a second time. However, I just changed my password on the database server and now Refresh Data gives me an ODBC error (Attempt to connect to the server failed.) I can't find a way to update the worksheet with the new password. Help please! >-----Origi...

Table with 'No Lines'
Using Publisher 2002 .... To prevent tables slipping I've tried Ctrl 'M' and creating a table on this page. I select all and colour the lines in black. (This creates a slipproof template) I then copy using Ctrl 'C' Back to original page, Ctrl 'V', the table I copied is now positioned exactly on top of the under page. I want to change this table into "No Lines" in order that I may type in the data using the 'tab' key to proceed to the next box. *exactly* but I'm unable to carry out this function. I'd appreciate guidance please. John ...

Changing Format of Access Field via Query?
HI all, Here is what i have, two Oracle tables across multiple schemas that I cannot change the actual format in the table. One table has the field = Text The other table has the field = Number/Decimal/10 Precision I need to link these two together to run some data and currently I have been making a temp table and changing the TEXT one to a NUMBER but am wondering if there is a way to change the Format of the TEXT one to a Number in a query which would save me from having to always be making a TEMP TABLE. Thanks in advance for any help you can give.. Aaron Try using an aliased column lik...

invisible graphics and tables
I have upgraded my computer to a P4 3Ghz and FX5600 256Mb Graphics Card and New Matrox Hard Drive.. Since formatting and re-installation of PUBLISHER i can not see my Tables or Word art and a few graphics only the Fonts are Visible. The documents print fine everything is there but i can not see them on the monitor. I made up a new File saved it then re executed it and the same thing happens. Have all updates in, changed refresh rates, latest Nvidia driver, Reinstalled Publisher and updates. PLEASE HELP ME BEFORE IT GOES OUT THE WINDOW!!!!!! Why is it that the first thing people wan...

Pivot tables lost in Excel 2003
Hi, We recently upgraded some of our machines to Office 2003, from Office 2000, however are now experiencing serious problems with Excel. It appears that when a file containing pivot tables (all files were created in Excel 2000) is modified with Excel 2003, the pivot tables somehow become corrupt. The next time the file is opened, Excel reports an error and tries to repair the file, dumping the Pivot tables due to a problem with their integrity. The exact message is as below; PivotTable report 'PivotTable1' on '[Book1.xls]Sheet1' was discarded due to integrity problems. Pivo...

Database query with parameters given in the sheet
Hi all, I've created a new database query with inner joins and sorting, which gives great results. But, there's only one thing I can't figure out on how to get this thing working... I'm using Office XP, and I try to make a sheet in which a user can specify a "from" date and a "to" date, after which the query starts only in this range. However, when I try to create the criteria, there's no way I can use variables or named cells in any way. Can anybody tell me a workaround for this matter? Maybe this is something for Microsoft to pun in new versions in...