Get Information From A Few Tables Joined Not Working....
Nov 28, 2007
Hi,
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 have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.
If I use order by on a query that has two linked tables I keep getting an error: This is the query SELECT d.PDetailsID, d.po_id, d.po_uniqid, d.job_id, d.p_qnty, d.p_desc, d.p_unitp, d.p_units, d.p_extend, d.p_qos, d.p_grec, d.p_done, d.cocreq, d.est_id, d.line, d.class, d.selected, d.itemid, d.category, d.GroupID, d.p_qosextended, d.late, d.lastsavedby, d.matid, d.customer, SQLACCESS.tblctpur_d.jobid, d.DTime, d.Notes, d.DescDetails, h.pur_ddue FROM SQLACCESS.tblctpur_h as h INNER JOIN SQLACCESS.tblctpur_d as d ON h.pur_id = d.po_id ORDER BY d.line
The error is : The colum prefix d does not match with a table or alias used in the query
What I'm trying to accomplish is probably really simple that I just can't get it to work. I have set up the relationship properly, I think. Here is what it looks like:
Each project will have three different member roles. Here is what I'm trying to get from my db:
myQuery(tblProjects.*, tblMembers.MemberID As Role1ID, tblMembers.MemberName As Role1, tblMembers.MemberID As Role2ID, tblMembers.MemberName As Role2, tblMembers.MemberID As Role3ID, tblMembers.MemberName As Role3)
I need Role1ID = Role1 If when tblProjMemb.RoleID = 1. I need to create a column for each of the three member roles and have the name of that person in that column.
I'm not sure how to accomplish this. Any help is much appreciated. Thanks!
I have two tables in my database Agents Zip Codes (linked table)
If I create a query and then a form off the query that includes fields from both tables - Access will not let me add new records.
Ultimatley what I am looking to do is have a zip code field in the Agents table that I fill in and then it will automatically look at the zip codes table and then fill in the zip code, county, & state in a different area of the form.
I tried using fields from both tables and tried a query using fields from both tables and it will not let me add new records. I have a relationship between the fields in both tables.
I have problem with pdf-ing a report that has record source coming from a query namely from the two tables joined based on the same of several fields (foreign keys).
I have PDF coding that would turn Access report to PDF report. It has no problem when the report's record source is from one table only.
Just wonder if anyone who might have a similar issue would like to share with me some help. Is it normal that PDF doesn't work if the report comes from joined tables in Access?
I can not, or not allowed to enter data in fields in a query built from three tables that are jointed by a common field with the same name. The parent table is linked to another Access data base as is one child table the other is local data. Also, I am not able to view the child table data when viewing the parent table.
I have at least 3 relates tables in my access database.the first(sessions) table stores session detailes like id,date,time
The second one (tblemployees) contains our employees details like name, idp ,the third (attreq) relates the first table to second it stores ids of sessions and ids of personals that which determine which personals have attended in special sessions.
Now,my problem is that I want when a personnel log in and opens "confirmed session"form ,the access check and open records that this person have attended. How can I do it?
I have made a crosstab query that is sporatically making duplicate counts. I'd like to know how to fix the problem, but more importantly - I'd like to know why and how my query is giving me these results so I can avoid making this same mistake again.
The relationship is set up so that all records from one table are returned, with only matching records from the other - so no problems that way as far as I can see. This is my crosstab query that is giving some counts of the same record as being matched sometimes two, three, or even four times:
TRANSFORM Count([Testing DB].[DB_ID]) AS [CountOfDB_ID]
SELECT [Testing DB].State, [Location].[North], Count([Testing DB].[DB_ID]) AS [Total Of DB_ID]
FROM [Testing DB] LEFT JOIN [Location] ON [Testing DB].[Address] = [Location].Address
WHERE (([Testing DB].[Window Length]) Is Not Null))
GROUP BY [Testing DB].State, [Location].[Size]
ORDER BY [Testing DB].State, [Location].[Size]
PIVOT Format([Date],"mmm-yyyy");
When I do a simple query for a list of the records being counted by this query, I can see the duplicate entries and can eliminate them simply by adding "Distinct" to the select statement - but I haven't had any luck adding any kind of distinction with this crosstab that will do the same.
How can I eliminate duplicate counts of records in my crosstab? If anyone has a suggestion, it would be much appreciated.
I have a query, that I have a criteria to show appointments in the past (< Date()) but one result doesn't show up although the appointment end date is a past date, it only shows up when I fill in a field that is in another table that is joined and part of the query. But there's no criteria there for it to not be null.
I am working on setting up a loop that will get information from a query. I can handle the rest of the code once I get the primary piece of information. Which at this point I can not get. I am trying to get to the next record of my query. For some reason I am stuck on just the first record and it is repeated the exact number of times equal to the number of records in the query. Here is what I have so far.
Code: Dim FSO As Object Dim FromPath As String Dim FromPathCheck As String Dim ToPath As String Dim FinalDestination As String
[Code]....
Yes I do know that I am doubled up on the
Code: rs.MoveNext
The reason is because with out this added rs it goes into an infinite loop. Need generating each value in the TRNumber field in the query.
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.
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 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'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.
A 10 10 B 15 15 C 20 12 D 25 9 E 30 20 F 35 18 G 40 2 H 50 23 I 55 1 J 60 18
I have to get the Top 5 for each Col, grouped by group, but all in one table. What I did was split Col1 to one query with a return of 5. And also did that with Col2. But now I have to join them, and only a few records pop up, those which have the same group name, all others were excluded...what can I do?
