what could be the probelm with my query? Here:
******************
SELECT tblPartnerProfile.PartnerType AS [Org Type], tblPartnerProfile.PartnerName, tblPartnerProfile.PartnerLoc1, tblPartnerProfile.PartnerStatus AS Status, Count(PartnerType) AS TotalType,
FROM tblPartnerProfile
WHERE PartnerLoc1 = 1 OR PartnerLoc1 = 2
******************************
I would like to have it give a count of how many [Org Type] 1 and 2 for Loc1 1 and Loc2 2.
SELECT Orders.DelAdd1 AS Street, Orders.DelAdd2 AS [Street 2], Orders.DelTown AS Town, Orders.DelCounty AS County, Orders.DelPostcode AS Postcode, Sandwich.SandwichName AS Sandwich, sum(Orderline.Quantity) AS Quantity FROM Orders, Orderline, Sandwich WHERE Orderline.Sandwichno=Orders.SandwichNo and Orders.DeliveryDate=[Please enter Today's Date] GROUP BY Orders.DelPostcode;
It says I've not specified street as an aggregate function, but I don't want to!
SELECT [TableA].[FieldA], [TableA].[FieldB], TableB.FieldC FROM [TableA],TableB RIGHT JOIN [TableA] ON [TableA].[FieldB] = TableB.[FieldD] GROUP BY [TableA].[FieldA], [TableA].[FieldB], TableB.FieldC;
What happened was that TableA has Fields A and B, and Table B has a field C and D. Tables B and D have the same content (Not necessary unique) and I would like to see a mapping from Field A -> Field C by joining on Field B and D.
However, when I ran it, I got an "invalid arguement" error.
please help, i have a form where i enter goods recieved vouchers,in this form if the cost price on the form is higher than the price in the Stock table then the price must be updated, if the price is lower then the price in the stock table doesnt need to be updated. I have a method which i have used before with no errors: i run an if statement on my Lost Focus() event procedure on TxtCostPrice it looks like this:
Sub txtCostPrice_LostFocus () Dim Cost1 As String Dim Cost2 As String Cost1 = txtCostPerUnit Cost2 = DLookup("price", "Stock Numbers")
If Cost1 < Cost2 Then
Dim DocName As String DocName = "updateIfCostPrice" DoCmd OpenQuery DocName, A_NORMAL, A_EDIT
MsgBox "Cost price has been updated"
ElseIf Cost1 > Cost2 Then MsgBox "Cost price does not need to be updated"
End If
End Sub this code links me to my update query, everything on my update query looks fine but when i run the code it doesnt seem to be updating.
Please help as my deadline is fast approaching at an arlaming speed!
I am not sure how this query I made is pulling up wrong data. Everything else is fine, except for 1 row of data. It is supposed to pull how many demonstations were scheduled for that day and how many were executed. It comes up with 4 demos and 8 executed. That can't be right. I checked it with the actual data and there are 6 demos total and 4 executed. Anyone know what could be going on there? For the other 30 something records it works just fine.
SQL: SELECT [Promo count].PromoNo, [Promo count].[# of Demos], Count(DemoOrder.Status) AS CountOfStatus, CountOfStatus/[# of Demos] AS Percentage FROM [Promo count] INNER JOIN DemoOrder ON [Promo count].PromoNo = DemoOrder.PromoNo WHERE (((DemoOrder.Status)="E")) GROUP BY [Promo count].PromoNo, [Promo count].[# of Demos];
I placed ="G*" in a text field to get all names that begin with G and a blank return was issued. Maybe the manual I have has the wrong info. I have Access 2003 and a Access 2000 manual. Please help.
If you open the form called people you will see a command button Called add charge once pressed will open a form called charge
On the Bill To combo Box I have the following Union Select Query
SELECT PrimaryInsurance, PersonID FROM tblpeople UNION Select SecondaryInsurance, PersonID FROM tblPeople
What I want the bill to combo box to display is only the Insurances that are related to the Patient that the charge is for and not to display other patients insurances
This is at least the 3rd time I've come across something that is, to me, an extremely serious bug in Access. Anyone else seen this, and anything I can do to avoid it?? In a query, I ask for the values from Field A. The query returns the values from Field B, but still calls it Field A.
This is in a fairly complex query. If I delete one particular field from the query, the bug disappears. If I put that field back, the bug returns.
Here is my current query: SELECT ByPN_1.PartNumber, ByPN_1.LostPerSetup, ByPN_1.ActualMachine, ByPN_1.PermQty, ByPN_1.FirstOfFeederSize, ByPN_1.Leaf1, ByPN_1.FirstOfType, ByPN_1.NonPerm, IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups])) AS QtyIfDed, [nonperm]*[setupsperday] AS MaxLoadsSavedPerDay, [qtyifded]*[setupsperday]/[concurrentsetups] AS DedLoadsSavedPerDay, IIf([maxloadssavedperday]<[dedloadssavedperday],[maxloadssavedperday],[dedloadssavedperday]) AS LoadsSavedPerDay, [loadssavedperday]*[laborrate]*[loadunloadperfeeder]*250/60 AS Labor_Annual, master_attr.STD_COST, [loadssavedperday]*[lostpersetup]*[std_cost]*250 AS Parts_Annual FROM (ByPN_1 LEFT JOIN FeederCost1 ON (ByPN_1.ActualMachine = FeederCost1.Machine) AND (ByPN_1.FirstOfFeederSize = FeederCost1.Size) AND (ByPN_1.FirstOfType = FeederCost1.Type)) LEFT JOIN master_attr ON ByPN_1.PartNumber = master_attr.ITEM WHERE (((ByPN_1.PartNumber)=107573));
HEre's the pertinent part of the record from BYPN_1: PartNumberLostPerSetupFirstOfFeederSize 107573 3 12X4
But here's what the query returns: PartNumberLostPerSetupFirstOfFeederSize 107573 12X4 12X4
Now, if I just delete the "parts_annual" field from the query: SELECT ByPN_1.PartNumber, ByPN_1.LostPerSetup, ByPN_1.FirstOfFeederSize, ByPN_1.ActualMachine, ByPN_1.PermQty, ByPN_1.Leaf1, ByPN_1.FirstOfType, ByPN_1.NonPerm, IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups])) AS QtyIfDed, [nonperm]*[setupsperday] AS MaxLoadsSavedPerDay, [qtyifded]*[setupsperday]/[concurrentsetups] AS DedLoadsSavedPerDay, IIf([maxloadssavedperday]<[dedloadssavedperday],[maxloadssavedperday],[dedloadssavedperday]) AS LoadsSavedPerDay, [loadssavedperday]*[laborrate]*[loadunloadperfeeder]*250/60 AS Labor_Annual, master_attr.STD_COST FROM (ByPN_1 LEFT JOIN FeederCost1 ON (ByPN_1.ActualMachine = FeederCost1.Machine) AND (ByPN_1.FirstOfFeederSize = FeederCost1.Size) AND (ByPN_1.FirstOfType = FeederCost1.Type)) LEFT JOIN master_attr ON ByPN_1.PartNumber = master_attr.ITEM WHERE (((ByPN_1.PartNumber)=107573));
Now the query returns what I expect: PartNumberLostPerSetupFirstOfFeederSize 107573 3 12X4
I have two tables Contacts & Allowance. There is a relationship defined between the two based on Contact ID (primary field/contacts - foreign key/allowance). The Contacts table has Firstname + Surname. I have produced a query based on certain fields in both tables however the result is totally pulling incorrect info.
My problems is that when I use below setup without the "Notes" tabel, they query works fine, and returns the amount of spares and time used by a single WBS ID. (Customer). But when I add another tabel and link it together. Its returns load of lines with the same data - it looks like its copying its self, over and over again.
I am using a MS access mdb file to display some record from oracle database using odbc connection.I have a table (linked table) called map_detail in mdb as well as oracle with same table structure.I formed one query in mdb (sql query) select * from map_detail where batch_no="SSO15121".It is always fetching some other result, but when I am changing the query by changing the batch no "SSO15148" it is working fine. I noticed that for cases it working.
My query references 2 related tables: one for persons (PERS) and one for telephone/fax numbers and email addresses (CONT, for Contacts). The relevant fields are:
The foreign key come_id refers to a table for contact methods (COME), either "Phone (Home)", "Phone (Work)", "Mobile", "Fax" or "Email".
Now i want to list all persons with their home phone number and email address, also if they don't have one. It seems to be impossible to get it ... I will explain what happens. Lets start simple: first list all persons with their home phone number (come_id = 1): SELECT PERS.pers_forename, PERS.pers_surname, CONT.cont_number AS Phone FROM PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id WHERE (((IIf(IsNull([come_id]),1,[come_id]))=1)); This works fine. The IIf expression is necessary since we are dealing with an outer join: not all persons have a home phone number. If we would simply put "WHERE come_id = 1" then the query produces only the persons that have a home phone number.
But now i also want to see the email address (come_id = 5): SELECT PERS.pers_forename, PERS.pers_surname, CONT.cont_number AS Phone, CONT_1.cont_number AS Email FROM CONT AS CONT_1 RIGHT JOIN (PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id) ON CONT_1.pers_id = PERS.pers_id WHERE (((IIf(IsNull([cont].[come_id]),1,[cont].[come_id]))=1) AND ((IIf(IsNull([cont_1].[come_id]),5,[cont_1].[come_id]))=5)); It seems perfectly logical: i added a second alias CONT_1 for the email address. Since this is also optional we have a second outer join, and the WHERE condition should also use an IIf expression. The result is not correct though: the resulting recordset shows only the persons that have both a home phone number and an email addres or neither! I have a lot of experience with SQL and queries, but i know i am not infallible. Nevertheless i am quite convinced that i should get all the persons: those that have a home phone number or an email address, or both or neither ... I hope that someone of you can explain this.
I am writing the following query to insert data in tblpostroom from another table. Both the tables have same field names like Date1,582,1810.Now If I run the following query then in the fields 582,1810 of tblpostroom , the data gets stored 582,1810 respectively for all the records. But in actual fields 582 and 1810 store different data.
Code: INSERT INTO tblpostroom ( Date1, 582, 1810 ) SELECT Date1, 582, 1810 FROM Sheet1;
all of a sudden now, when i open my form, its not displaying any of the previous data that i stored in it!! All the data is still in the table, and when i add data it goes into the table!! I checked properties and the record source and everything is ok there, what have i done wrong guys...HELP!!!!
I purchased a copy of Office 2003, on Ebay, for less than half the price it normally sells for. I created databases, which work well on my computer. However, when I copy them over to the office computer from my computer, via Flash memory stick, the program does not run. The message seems to indicate a problem with registration. When I installed Access, it asked me for a registration key, which I did provide. After that it worked-not activation process. Does this mean that you can only use the computer you created the database on to run it, or that I do not have a proper version of Access 2003? I cannot transfer, from my home computer, to the office computer. Is this Microsoft paranoia again? Subsequent uninstalls and reinstalls of the program did not ask for the installation key. What should happen? I would like to resolve this major problem and your help will be greatly appreciated. The program is flaky. Also, Switchboard does not work-must use Option Box.
These questions most user must know about initial installation and copying mdb files to other computers -please reply.
i have a string variable denoted by cui and i need it to take value from table tblPersoaneJuridice from field CodUnicInregistrare. CodUnicInregistrare and NumarInregistrareRegComertului is text.
cant get it running. i got the same query running made in the querybuilder, but somethings change, so i need to adjus it in vba. But it doenswork, and get a type error
DoCmd.RunSQL "INSERT INTO tblAccessLog " _ & "(DateLoggedOut, TimeLoggedOut) VALUES " _ & "(#" & Date & "#,#" & Time & "#) WHERE " _ & "(LogID = '" & LogsID & "');"
Could anyone possibly tell me why this isn't working? Probably something stupid i've done bus still.
When i run it, I am getting the error message "Query input must contain at least one table or query" yet i've told it to use tblAccessLog so i'm just confused.
