I have a query I'm creating that is using a joined view. I'm joining two tables and whereas the first contains unique data to each field the second contans class information so for example while all users are unique many may be assigned to the same class. When i use the join it therefore dupliactes all the class information in the query.
how can i set up the query so that the class information is only output once and not duplicated along with a user every time ?
I have a field in a database that needs the data entered without any spaces and I'm trying to figure out how to set either the validation rules or the masks so that the system will either automatically delete any spaces or warn the user not to enter them.
My preference would be for them to automatically be deleted.
Any ideas, suggestions?
I can figure out how to make either all numbers or all letters, but the data is both so that doesn't work.
what I am trying to do with that table is get a sum of all assets, a sum of all Customers, and a sum of all Employees only counting each Comp ID once. Each company that we deal with carries multiple of our products. So as you can see I currently have it in the table where it shows the different data for each product over 2011, 2012, and 2013. Obviously for this I cannot just sum the entire column, as I would have the same company summed multiple times. The thing that is making this challenging for me is that I need it summed for each individual year (2011, 2012, 2013), with the end result being a total number of employees, assets, and customers.
I was thinking that to do this I would need a Sumif formula with several other formulas inside of it, but cannot figure out what exactly I would need to do. I know that if I took it into excel I would be able to do it, but we have over 7000 different customers, each having anywhere from 3-20 different products, and for each of the years 2011-2013... so the data is just too massive to try to convert and manipulate in excel.
The reason that I am hoping to get a count of their customers, assets, and employees is so that we could then take the info from individual customers and find out that particular customer's portion of the total assets, customers, or employees. I guess the mock table that I made doesn't keep the formatting when I add spaces, so imagine that each pair of l l is a column and match that with the columns above.
I also need another column that has the 2012 and 2013 results
l Comp ID l Year l Prod Fam l Prod Group l Prod Code l Assets l Customers l Employees l Assets for 2011 l Customers for 2011 l Employees for 2011 l l 1101232 l 2011 l Family A l Group A l Prod A l 59000 l 33 l 28 l l 1101232 l 2012 l Family A l Group A l Prod A l 55000 l 36 l 23 l l 1101232 l 2013 l Family A l Group A l Prod A l 51000 l 39 l 25 l l 1101232 l 2011 l Family A l Group C l Prod B l 59000 l 33 l 28 l l 1101232 l 2012 l Family A l Group C l Prod B l 55000 l 36 l 23 l l 1101232 l 2013 l Family A l Group C l Prod B l 51000 l 39 l 25 l l 1101232 l 2011 l Family B l Group B l Prod C l 59000 l 33 l 28 l
I work with an Access database with about 20 tables that requires considerable manual data entry. Although it is time consuming, the bigger problem is the keypunching errors that inevitably occur. A colleague of mine said that some databases can be set up so that two (or more) individuals can enter the same data into the database (basically creating two separate, temporary databases). At the conclusion of this double data entry, the two databases can be compared to see where they differ so that corrections can be made...the logic being that it's extremely unlikely two people entering the same data independently will make the same keypunching error in the same field.
Just wondering if Access has this capability, or if a reasonable facsimile of this approach exists.
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 am have trouble eliminating the $0.00 balances in a query. From what I can figure out is that even though the Balance appears as $0.00 in reality it could be anywhere from $0.0012 to $0.009 or something like that.
What I am trying to do re reconcile the Balance field. If the balance reads $0.00 I want it out of the query. Then I would like to find all the balances that appear to be $0.01 to a negative $0.01.
It seem that no matter what is use as critiera I still can't what I want.
I have a query for clients who come to our office looking for services. I only need to report 1 visit per client during the month, in other words if the client comes several times during the month, we only count 1.
When I run the query, it gives all the times the client came to the office, but I only need to show once.
Clients are being sorted by ClientID. What do I need to write in the Criteria field to filter clients from showing again ?
I have many queries that all do the same thing. The problem is that they're connected to specific controls on a form, so I have to make a new query everytime I make a new form to perform the same task as the others. This would be eliminated if there was a way to use the criteria the same way you would in code, like Me!controlname. Is there anyway possible to make these queries more universal so that they can be used by many forms?
I have created a query which gives me a COUNT for a field type GROUP: SELECT AllPairAssignmentsQ.COID, Count(AllPairAssignmentsQ.COID) AS CountOfCOID FROM AllPairAssignmentsQ GROUP BY AllPairAssignmentsQ.COID;
AllPairAssignmentsQ is based on a union query of 5 tables. The problem I am having is that I am getting some duplicates which I need to eliminate. Each listing contains several fields. However their are two fields in the query which can be used to determine that entries are duplicated. If the combination of MAINCOUNT and COID are identical in an entry then only one of the listings needs to be counted.
The following is an example of my problem: MAINCOUNT COID 1201 HNVL 1202 HNVL 1203 HNVL 1203 HNVL 1203 COAC 1204 COAC
My existing query will return the following: COID COUNT HNVL 4 COAC 2
Since 1203 HNVL is duplicated I would like for the query to count only one entry so that the query returns the following: COID COUNT HNVL 3 COAC 2
Thanks for any help that can be provided in solving this problem
i have a main form named(EMP) i have a subform named(SEMP)with EMPID i have an another form Named(SDetail) with EMPID i want to open form Sdetail with filter records for data select in subform (SEMP) ,EMPID field Subform SEMP in as datasheet view. i can open sdetail for selected records only
I am building a query that pulls from one databases but I am running into duplicates.
Here is the skinny:
From the [tbl_databases], I am setting criteria that restricts a column containing “Visio”, more specifically, “Not Like “*Visio*”. I am also setting criteria that restricts a column containing “OLD” (it is a Yes/No field).
.
The problem is that there are duplicate rows that contain both “Visio” and “Old. So, simply suing my criteria restriction are pulling out the Visio and Visio/Old orders as well as Old and Old/Visio order.
Can I create a subquery that contain the combination of “Visio” and “Old” to restrict the subset from being extracted twice?
I think I have the logic but not sure not to set it up.
OK, first time posting so I'll try to be clear here. I work for a special education agency and have created a database to track student/therapist information. Now, I have reports showing which kids are in which schools and who their therapists are. What I can't figure out is how to print a report, grouped by school, that will display which therapists are active in that school.... any ideas?
I am currently looking for a way to add data in a form with picture and then be able to view all entries with data.
Basically what I want to see is "product image > item number > client > description > material" with for example 500 entries, i want to be able to see a whole list of all entries. I would like to be able to make every entry as described above.
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?
I have produced a label printing query which eliminates duplicate addresses from a mail list. It works beautifully.
However, when I try to replicate with a different field criteria the new query fails to produce any output at all.
The SQL code I am using is as follows:
SELECT a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-Mail List] FROM [Mail List] AS a GROUP BY a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-Mail List], CStr([a].[Member ID])+[a].[Address 1] HAVING (((a.[e-Mail List]) Is Null) AND ((CStr([a].[Member ID])+[a].[Address 1])=(Select cStr(Min(b.[Member ID])) + b.[Address 1] From [Mail List] as b Where b.[Address 1] = a.[Address 1] Group By [Address 1])));
The working version has 'HAVING' e-Mail News =False instead of e-Mail List is Null. The former searches for an empty check box and the latter for an empty field.
Linked as indicated, (all from tblSurvey to those matching tblJobs) with criteria, WHERE jobcode is null Ie. an unmatched query between tblSurvey and tblJobs.
Now I want to delete those from the survey where there is no jobs.
But when I change my select query to a delete query, I get the error: Cannot delete from specified tables
My SQL (access generated) looks like this:
DELETE tblSurveys.*, tblJobs.JobCode FROM tblSurvey LEFT JOIN tblJobs ON (tblSurveys.Grade = tblJobs.JobGrade) AND (tblSurveys.PayType = tblJobs.JobPayType) AND (tblSurveys.Survey = tblJobs.JobSurvey) WHERE (((tblJobs.JobCode) Is Null));
That first line looks wrong...
Anyway, if someone can help me to delete these I will be forever grateful.
The way I understand it, a delete query should look more simple... ie delete * from tblName where (and type the intricate stuff here)
Thanks in advance Reenen
(BTW: I posted this on Ozgrid as well, but they are bigger on Excel there, and response times are better here. Apologies in advance)
Hi, I have two tables A and B that I joined using a LEFT JOIN. I know that some rows in table B will not be joined. How do I count how many will not be joined and how do I return those rows? Thanks ClaudiaM
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.
With a particular client, they want to display a subform in datasheet mode. They do not want an alternative like continuous forms. If I am displaying the subform in datasheet mode, is there any way to eliminate the header row which contains the names of the columns in the subform? I know how to remove the captions themselves but the header row is still there. I don't think it can be done but I wanted to check here with those more familiar with this.
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 have created a query that show all the records form Material Estimation table that the job number matches in the Production Table, that the AssDate is between the frm_Export_Data dates.
This the SQL view of the query.
DELETE [Material Estimation].*, Production.[AssDate] FROM [Material Estimation] INNER JOIN Production ON [Material Estimation].[Job Number] = Production.[Job #] WHERE (((Production.[AssDate]) Between [Forms]![frm_Export_Data]![FromDate] And [Forms]![frm_Export_Data]![ToDate]));
I want to delete all the records in the Material Estimation table that show up in this query.
