In the scheme below, I want to fetch all the info in Table3 for all the Tiers that has the AppID in Table2. There could be more than 1 Tier using the AppID. I don't know if I'm clear though.
Code:
Table1 Table2 Table3
AppID (PK) _ AutoNb (PK) AutoNB
Name Tier (FK) ---_ Name
- AppID (FK) \_ Tier (PK)
Here's my failing attempt...
Code:
SELECT Table3.*
FROM Table3
WHERE Table3.Tier = (SELECT AppID
FROM Table1
WHERE Table1.AppID = 2002);
I have list box of reports in a form and when selected i want to write the reports description propterty to a textbox.
I modified an example i found posted someplace (can't recall where) and it worked great - just like this (see below).
Then I put it into another database (exact same tables, form and and queries) and if there is a description in the query property it will always give me the error "Type mismatch".
Why? The only difference I can see is that the working example db had the following References selected: Visual Basic For Applications Microsoft Access 9.0 Object Library Microsoft DAO 3.6 Oject Library ... in that order.
My database where it is not working has selected: Visual Basic For Applications Microsoft Access 9.0 Object Library OLE Automation Microsoft ActiveX Data Objects 2.1 Library Microsoft DAO 3.6 Object Library
Is there a better way to fetch this property? Or is there a setting I need to change - keeping in mind that changing these settings may cause the rest of my db to fail now...
Thanks A lot for any help you can give.
Function ReportDescription(ReportName As Variant) As String On Error GoTo Err_ReportDescription Dim db As Database Dim con As Container Dim doc As Document Dim prp As Property Set db = CurrentDb() Set con = db.Containers("Reports") Set doc = con.Documents(ReportName) Set prp = doc.Properties("description")
ReportDescription = prp.Value Exit_ReportDescription: Exit Function
Err_ReportDescription: If Err.Number = 3270 Then ReportDescription = "There is no description for this Report" Resume Exit_ReportDescription Else MsgBox Err.Description Resume Exit_ReportDescription End If End Function
Private Sub lstReports_Click() Me!txtReportDesc = ReportDescription("rpt" & Me!lstReports) 'Me!txtReportDesc = ReportDescription(Me!lstReports) End Sub
I am new with Access and fear that this might be a very elementary question - apologies. Basically, I would like to create a few different tables that all have the same base information (like the primary key and name of the data), but different additional information. The only thing that I can't seem to figure out is how to be able to add a new entry to one table and have it show it up in the other tables. Is this possible? Thank you!
Here is my problem, as you will see. In the from called frmStaff, I have created 2 lookup tables that work fine in the main form. In the subform under activites though, I have attempted to create a lookup form for Name and Location of activities. It does not appear to be storing the information from previous entries though. My aim is so that when someone goes to enter a new activity name or location, the data will be saved and be in the list for the lookup next time with a different entry, however, it does not appear to be storing the information in the appropriate table. Any ideas on how to fix this?
I am creating my first database and I'm a beginner. I have a table called customers and another table that has customer and product information. What I would like to do is when a new record for my product is entered it also enters the customer information on another table. I have repeat customers though, so I only want it to enter it once. Right now, I have to enter the customer info separately. Customer table contains: Customer ID, first, last, Phone. My product table also has this same info. Is this possible? To enter info on a product form and the selected info be copied into another table at the same time only once.
I am getting information on products stored in a query. I want that query to check a couple tables to see if an item is linked through all of them. If it is not listed in the last table (catalog) I want it to be shown.
Code:Catalog tableVolume Prefixprodno Price15 - CE 0218 9.9915 - CE 0722 3.7215 - CF 0218 12.3615 - CF 0091 14.00Source TableSource MediaCM70904 15 - CE
Code:Batches QueryCustomer Number Prefixprodno Source716933 0218 CM70904716933 0408 CM70904
This is the Select Statement I'm using, which gets the 0218 but not the 0408 I want
Code:SELECT (fieldnames......)FROM [Batches] INNER JOIN ([Source] INNER JOIN [Catalog] ON [Source].[Media] = [Catalog].Volume) ON ([Batches].Source = [Source].[Source]) AND ([Batches].Prefixprodno IN ([Catalog].Prefixprodno))WHERE [Batches].[Customer Number]=716933;
If I do NOT IN I get both 0218 and 0408. I just want 0408. I'm sorry if this seems confusing but any help would be amazing at this point =/
I am brand new to building a database.What I want is a database to store Quote, Job and Invoicing information. We receive quotes first and then they can, but don't always, turn into jobs. We can also receive a job without quoting it. We currently have two spreadsheets. One is for Quotes and the other is for Jobs.
QUOTES INFORMATION Quote # Date Customer Name Part # Part Name Quote Due Date Qty Lead Time Price Unit
JOBS INFORMATION Job # Qty Quote # Customer Name PO# Part # Part Name Est Hours Start Date Due Date Price
As you can see a lot of the information in the Quote spreadsheet is also used in the Job spreadsheet. (Bold represents duplicated items) We currently type the information into each spreadsheet.
Then there are different forms that are filled out for quotes and jobs that contain the information in the spreadsheets.Is there a way that I can have the QUOTE Table automatically populate the JOB Table information?
I have a form that launches a query. The results are displayed in a continuous form called ParentForm. On the ParentForm is a combobox which selects a singleform called a ChildForm that displays the details of the selected record. I placed a command button on the ChildForm. This button simulates a circular triple state toggle switch. By clicking this switch the form goes from ReadOnly to Edit to Add modes. The form properties AllowEdits , Allow Additions and DataEntry are adjusted accordingly. These properties when they are changed seem to initiate requery of the underlying data source. Here is the code. I am looking for a way to avoid requery of the data when the mod is changed.
Code: Private Sub ModeBt_Click() '------------------------------------------------------------------------------- ' Circular toggle button to change display mode of the form ' ReadOnly - Edit - Add '------------------------------------------------------------------------------- Select Case Me.Mode Case "Edit Mode" Me.AllowEdits = False
I'm trying to import information from an xls file into an Access database.. I understand how to use the File --> Get External Data --> Import option in Access, however I only have the option of importing the information into Sheet1, Sheet2 etc
I already have a table named Info1 which I want to import the information into
I have an historical database table that contains information relating to soldiers. Additionally I have many photographs, and other documents appertaining to these individual soldiers stored in folders on my PC hard drive and I would like ideally, to be able to have a link from my database to these other items, so that when I open a a soldiers record I am able to click on a link that will take me to a folder containing the photographs, letters and further documents relating to him.
However this is where I am at a loss as to how to proceed, and how I could have this facility, I run Access 2007 by the way.
I essentially created an account since I can't seem to find a straightforward answer much anywhere else (plenty of hints on auto-filling forms- but that isn't particularly conducive to my specific need on this).
I've attached a blank copy of my database.
The immediate concern I'm having is that I want to be able to autofill data in the invoice table based on the customer table and the products/service table.
*I want to be able to select a first name [or other primary key if necessary] to fill in the last name, company, address, city/state/zip, phone, fax, cell/alt phone, and email automatically... that is without multiple dropdown selections or input to those sections at all.
*I want prices to fill in to the 'cost of product/service X' so that I may use it for other calculations in the invoice table- as well as to make forms from it directly.
I have created two tables. One table list of 100 Categories that I monitor each month. The Categories are never changing month-to month, however, each might be associated with a different client month-to-month. Presently, I am manually typing in the Client information month-to-month with information pulled from the Client's table. The Client's table has a Category Field which is populated when a Client has purchased space to use it.
In essence, I have the Category Table (Fields: Record Number; CategoryName; Client Name; beginning date the client will use the Category and Ending Date the client will stop using it). The Client's Table has a lot more fields/information but it still has the same fields as the Category Table. I am trying to be able to use the Category Table and have it automatically populate with the client who is using the Category at that time. Any Category not being used by a Client then the Query should write "Open" in the Client's name field. I have tried many different scenarios but can't seem to get it to work. I must see all 100 Categories each time I run a report. It doesn't have to say "Open" but where ever a client did not use the Category it should be left blank.
I am tracking extreme useage on account numbers. I've set up a table to add the information on a daily basis.
Currently the table has the following Fields:
Report_Date / Account / Usage / Sent / Received / Comments / Notes (the Sent and Received fields are Y/N)
I've gotten a report to send working as I want, but I am running like 6 queries to put all the data together correctly. For example; if an account was flagged today and sent today I do not want to send another notice for the next 5 days; but I still want to log the data in the table for historical reasons... Again, this all works. Just that it is not pretty..
The part that I keep fighting is the responses. When I receive a response I want to log that. Right now, I check the Received flag, and add in any comments the customer sends back. This is easy. But I also want to print that information out on the report - just the latest information. For example, if I send a notice out on 11/1/12, 11/18/12, 12/14/12, and 1/10/13 and I have responses back from all of these logged in the table, when this account number gets printed again - say today - I want to add to that report JUST the comments from the 1/10/13 report.
I have now been asked to create 'something' (form, query, whatever) that will:
Allow the engineers to change the date in multiple tables for parts; and,Change (in)activity status information in those tables; and,Delete the same obsolete part(s) from two tables and add that information (one entry) into another table.
is there anyway you can create one form which updates all the information in multiple tables. I need to create one of these to make the navigation of the database more easy, so if anybody could help me it would be much appreciated.
I have tried on different forms using more than a few different methods to make recordsets happen and to no avail. My current reason for needing to use recordsets is because I am loading a form with information from different tables. Instead of using multiple DLookups, opening the recordset only looks for the information once, speeding up the load time on the form.
Code: Dim Rs As Dao.Recordset Set Rs = CurrentDb.OpenRecordset("PreviousTRRetrieval2Query") If Rs.RecordCount > 0 Then ProjectNumber = Rs!ID Agency = Rs!WitnessAgency FileNumber = Rs!FileNumber
That is the start of my code and I keep getting an error, 3061 Too Few Parameters. Expected 1. Now when I run the query I get the results I desire, which is all the information I need at this particular junction. I am using Access 2010. Why I may not be returning any results for my recordset when the PreviousTRRetrival2Query returns results on its own.
I've got a table which lists accounts with an associated charge. If the account has multiple charges, then the account appears multiple times, for each charge. I'd like to consolidate the duplicates without losing each charge that is associated with the account.
I have given an example in the attached excel spread sheet with a few comments at the head of each column.
1. I want to confirm that my best modelling approach for this kind of information is a series of 1 to many relationships linking the attributes in columns A->D?
2. In the example given an environmental rating is given to each make/model of car based on each combination of attributes A-->D. How would this work in terms of user form design to enter the necessary information? in order to assign an environmental rating?
I'm having some trouble getting my information that I input on my form to store in my correct table. I will attach my DB so you can take a look at what I have thus far.
Here is what I am wanting to do:
I have TblEmployee, TblEquipment and TblJunction and FrmTracking and FrmUpdate
I input the bulk of my information thru FrmTracking, my trouble is I can not seem to get the information that I input in my FrmTracking to store in the correct table. I can get the information to store in TblEmployee, however the information that I want to be stored in TblEquipment will not store in there.
I have a query that gathers information from the tables in my database and returns something like this:
Code: Field Pest 1 Insect1 1 Insect2 1 Insect3 2 Insect1 2 Insect3 2 Insect4
Field and Pest are fields in two separate (but joined) tables. Field exists only once in its table, and multiple pests are attributed to each field. I'm looking to create a table or query in which the values would be listed as follows:
Code: Field Pest 1 Insect1, Insect2, Insect3 2 Insect1, Insect3, Insect4
The purpose for this is to create a legend for a map used by another program based on the information gathered in the database. I do have other criteria for which fields and pests show up in this query.
What would be the best table construction to allow for the inclusion of area information that varies depending on the selection of a certain route, to also include crossovers?
How best to allow users to see all of the areas that apply to each of the routings, but not to be presented with the same grouping twice if more than one routing is selected at the same time?
I should add that I am looking to have these selectable by a combo that is filtered according to the value(s) in other fields.
I have a table of events that includes fields for ages, a Start Age and a End Age.
I want to make a query that pulls up how many programs have been provided to a given age group. How can I make it so that the query will understand to include records that have the given age group in between the Start Age and End Age fields? I.e. if I set the query to 3rd Grade, records that start at 2nd Grade and end with 4th Grade are included in the results as well.
