Basicially I have two tables, which are Linked tables and they are linked to two Excel files. Each of the tables has almost the same information: Stock Number, Location and Lot.
The purpose of this query is to check Quantities on both spreadsheets to see if there are any discrepancies. Query works fine if Stock Number, Location and Lot are the same on both spreadsheets but if they differ somehow i.e. Stock Number and Location are the same but Lot is different then Access doubles/triples records.
I have a feeling there is an error in Relationships. I googled it and I may need to set-up many-to-many relationship but the problem is that Access does not alow me to do it. I created a junction table but then I cannot set-up one-to-many relationship between tables, which I think is because tables are Linked.
Code:
SELECT DISTINCT [Query Union].[Stock Code] AS SKU, [Query Union].[Location Id] AS [Location No], [Query Union].[Lot No#] AS Lot, IIf(IsNull([TMP2 SOH].[Physical stock]),0,CDbl([TMP2 SOH].[Physical stock])) AS [TMP2 Qty], IIf(IsNull([TMP1 SOH].[Good Stock]),0,CDbl([TMP1 SOH].[Good Stock])) AS [TMP1 Qty], [TMP1 Qty]-[TMP2 Qty] AS [Variance (TMP1 - TMP2)]
FROM ([Query Union] LEFT JOIN [TMP2 SOH] ON ([Query Union].[Stock Code] = [TMP2 SOH].[Item number]) AND ([Query Union].[Location Id] = [TMP2 SOH].[License Plate Id]) AND ([Query Union].[Lot No#] = [TMP2 SOH].[Lot number])) LEFT JOIN [TMP1 SOH] ON ([Query Union].[Stock Code] = [TMP1 SOH].[Stock Code]) AND ([Query Union].[Location Id] = [TMP1 SOH].[Location Id]) AND ([Query Union].[Lot No#] = [TMP1 SOH].[Lot No#]);
I have another problem with a query I am doing today. When I run the query it brings up everything I need, but 5 of the Promo#'s are the same, but they have different # of Executed in them. They should not be like this, they should be together in 1 Promo#, not broken into 2. Here is my SQL code:
SELECT [Promo count].PromoNo, [Promo count].[# of Demos], [Promo Executed].CountOfPromoNo AS [Executed Demos], IIf([Promo Executed]![CountOfPromoNo]=0,0,[Promo Executed]![CountOfPromoNo]/[Promo count]![# of Demos]) AS Percentage FROM [Promo count] INNER JOIN [Promo Executed] ON [Promo count].PromoNo = [Promo Executed].PromoNo GROUP BY [Promo count].PromoNo, [Promo count].[# of Demos], [Promo Executed].CountOfPromoNo;
Its taken from 2 queries Promo count and Promo Executed.
I have a table which displays playerID, YearID, RosterID, and Games played. Then I have a query that looks like this
SELECT Positions.playerID, Positions.YearID, Positions.rosterID, Max(Positions.[Games Played]) AS [MaxOfGames Played] FROM Positions GROUP BY Positions.playerID, Positions.YearID, Positions.rosterID;
My problem is that I dont want any doubles so if a player have 2 rosterID's in 1 year I just want to see that roster ID associated with the MaxofGamesPlayed. How do I do this???
I am a novice to db design but I started my db quite a while ago but have only just noticed it keeps doubling in size periodically, even though I have not added many records or changed much. The first time I noticed this phenomena it was at 65Mb (already too big?), it then periodically (and randomly) doubled in size until it was about 480Mb!! I have been told that when db objects are deleted they are not actually deleted and this would explain the size explosions I saw. I have compacted and repaired the db and it now stands at a much more likely 11Mb. However, I really wanted to understand why this occurs so that i could prevent it from reoccuring (I have set my db to compact on close now - which seems to keep it small in size).
I have a whole load more questions aswell but you will see those in the near future!!
When I attempt to query these tables, my totals increase. EX: I have 500 items sorted; 100 defects; 2 sorters; 2 operators and 100 defects of type 1 and 100 defects of type 2.
My query results are 1000 items sorted with operator 1 having 100 defects of type 1 and 100 defects of type 2 and operator 2 having 100 defects of type 1 and 100 defects of type 2.
My quantity of items is doubled because it took (2) operators to make the items. My defects are doubled because it took (2) operators to make the defects.
I delete the old 10MB table and import a new 10MB table and now the the DB is 20MB - why is not 10MB - number of records is about the same not double. DB works fine and you can't tell any difference except the size.
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
I have a database that is used to create Quotations. After all of the information is entered the queries that hold the calculations must be run. I have lots of calculated that rely on other calculated fields. When I need to Sum all of the calculated fields in one field I must create a new query. I currently have a QuotationID, PartID, and MetalID all linked together. The first of the calculations are done per Metal, and these are working fine. I run into a problem when the calculations need to be done by part. My Queries are creating a record for every Metal and this is throwing all of my numbers off.
I have a query that will draw down student details who have completed a course in a given month (May for example), i would like to use this data to identify those learners who are not enrolled on a course in the next Month (June for example). There is no field that denotes whether a student has left only that a course assosciated with their ID has a completion date within that Month. There are approx 250 records.
In my head it should work something like this
1) Identify all learners who finsihed a course in May (Identify learner ID, must have a course end date in that month)
2)Cross reference these against all those who started a course in June and identify the students that have completed in May but did not start a course in June.
Is it possible to store all those who completed (May) in a table/query and cross reference those who started in June and identify of the May completions who did not start in June?
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
I have two queries which pull fuel uplift figures by airport from a master table, each query for a separate airline.
Each record is made up of Airport, Fuel Uplift, Month and Airline. I want to create a query that will show each Airport per month with the total fuel from both airlines. To do this I've created a new field titled AirportMonth and then applied a JOIN command. This works to a point but initially only returns records where the same AirportMonth value appears in both tables. I can alter the join properties to show all records from one of the tables but I need all records from both tables. Below is the SQL I have so far.
SELECT JAFyr.Airport, JAFyr.Month, [JAFyr]![Fuel Uplift USG]+[TFLyr]![Fuel Uplift USG] AS Expr2 FROM JAFyr LEFT JOIN TFLyr ON JAFyr.AirportMonth = TFLyr.AirportMonth;
I have a survey database that I've been using for the last year for monthly auditing of employees files. I need to be able to get monthly audit scores for each employee but grouped by their manager. The problem I'm having is employees have moved between managers throughout the year, so employees that are listed under Manager 2 now were actually working for Manager 1 when the audits occurred.
ie. Audits occurred Jan - April for Employee 1 while they were assigned to Manager 1. Employee 1 moved to Manager 2s team in May. So when running monthly reports for the year Employee 1 audits should fall under Manager 1 for Jan-April and Manager 2 for May-Dec.
I do have a history table set up like: tblEmployeeHistory ID (PK) EmployeeID (FK to Employee table) ManagerID (FK to Manager table) MoveDate (date employee assigned to manager)
The Employee table is set up like: Employee ID (PK) EmployeeName ManagerID (FK to Manager table)
The Manager table has the ManagerID and ManagerName.
That's the employee side of things; then I have the tables that store the audit results:
tblAudit AuditID FileNumber AuditDate EmployeeID (FK to employee table)
tblAuditResults AuditID; QstnID (Composite PK, QstnID is FK to tblQuestions) Answer
How can I use AuditDate and MoveDate to relate audits to the managers the employees were under when the audits occurred?
I currently have an Access front end backed up by SQL server.
I have 3 subforms that need to use a Access query that is based on a linked SQL table. It does everything I need but let me add new records. Upon checking the linked table has an enabled new rec button but the query doesn't.
Does anyone know what I need to change to get the queries to allow new records to be added since each subform has its own query?
I am wandering if it possible to check programatically whether a query returns a NULL result?
I am writing a program(VB) that involves an IFF statement where if there are records in the query it does a specific calculation & if there are no records returned in the query it does something else. Is this possible to achieve?
I thought I had just about finished my DB but now Ive printed out and checked my reports I notice I have several records in different types of reports showing duplicate records. Iv'e gone back over the queries and there are one or two duplicates in several of my queries that I did not notice before because there are are only the odd one or two. No matter what I do I can't stop this happening. I think it must be something to do with the dates in my payment table. PaymentID is key field - foreign key is MemberID. One member can have several paymentID's a new one every year when membership renewed. The problem seems to be when the member has two payment dates in the same year. This shouldn't be anyway but the database has not been used properly with entry dates missing, written over of wrong dates etc. Ive tried to correct this but do not want to tamper with past payment date records. I already have 'select distinct' in my queries and have tried 'distinct row' which seems to return even more duplicate records. Im pulling my hair out over this there must be a way to return the records from members showing just 1 only of their very latest payment record/date. Im using this expression in the query.
LastPaid: (SELECT MAX(PaymentDate) FROM S_Payments_Table WHERE S_Payments_Table.MemberID =S_Members_Table.MemberID)
(Phone usage report) I have 3 queries, Query 1 counts number of calls in for a phone extension. Query 2 Counts the number of calls out (dialed calls). The information is keyed on the phone extension. Query 3 runs the first 2 queries (By date via parameter). Each query is keyed on Extension, I only want to see the number of calls in and out for each extension. Problem, when a phone extension being counted has no calls (In or Out), it leaves the phone extension out of the report. I want to see all the phone extensions info regardless of the count (if it = 0). Does anyone know a way around this? Thanks in advance for your help.;)
Whenever I have a form that is based on a query, I am not able to edit the data in the form. Is there any way to change that? I have all allowances set to yes and Data Entry also set to yes. The form contains data for a single record.
where each number corresponds to the primary key in another table, each number being a company name (not really relevant). What I want to be able to do is:
1. Produce a query table result with the Total Project Value (sum of all values) each company (305-9) is involved in. For example, in Project A and C, 308 are not involved, so the 308's Total Project value would be B+D+E+F Valuation.
2. Produce a query table result with the Average Project value. Similar to above, but taking the total and dividing it by the number of projects they were involved in. For example, 308 had a total of 259 over 4 projects, so an average of 64.75
3. Produce a query table result where it takes the most common role for each company ID. For example, Company 308 was Civil 3 times and Structural 1 time, giving 308 with Civil as their most common role.
I am using the following code to get results from next month,
Code:
Month([ptdate])=Month(Now())+1
which works great to get next months results. I try to get what is left of this month by adding the code:
Code: Between Now() And Month([ptdate])=Month(Now())+1
This actually gives me results from July and August, eventhough it is Sept.Basically I want the results from the remainder of this month until the end of next month.
I'm looking for a way to fill out 5 records in one row in a table at once. I want to update a supplier and price per unit for supplier via a form. I have price per unit 1-5 and the price will be the same for all 5 records. Is there a way to do this through append or update query and if so which is best in my situation?
I need to find records for the day before after 5 pm and today before 8 AM,
I'm using access 2010, the field type is a timestamp, (mm/dd/yyyy hh:nn:ss)
I'm doing it manually using date()-1 and date(), then eliminating the records before the day before that we received before 5 pm and eliminating the records we received after 8 AM.
In my query I determine one of the fields using nested conditional statements
e.g. iif(component =1 and fred = "z", component, iff(component = 2 and fred ="x", "stuff"), "xxxxx")
(this may not be syntactically correct but bottom line I want to stick xxxx in field if conditions are not met and if that is the case I don't a) want that record in the query or b) I don't want to print the record in the report.
I have tried using the criteria in the query (<> "xxxx") but still get the record. Not looked at seeing if I can remove it from the report somehow.
I have been hitting my head against the wall to have my query not show any DUPLICATE STOCK CODES in my report..I have two "AQN.PR.A" records, I only want my report to print one "AQN.PR.A"...I have three "ALA" records, I only want my report to print one "ALA"AQN.PR.A
