I'm trying to create a database project for college and have run into a problem with a query. I am trying to find all bookings that have taken place in the last month, but with added details from other tables.
I have taken the job details and date (with validation for the last month only) from my Jobs table, and this works perfectly. However, when i try to match customer IDs to their names (stored in a seperate table), Access returns the same job multiple times with every customer name possible.
Any help you can give me to return just the one result needed would be greatly appreciated.
I am a novice with Access and would like a steer with what I am sure is a simple issue but I can't find an answer. :confused: I currently have 2 queries based on 2 separate tables.
Ops_Log_996_Query SELECT [996_Table].Unit, [996_Table].Location, [996_Table].hiredate FROM 996_Table WHERE ((([996_Table].hiredate)=Date()));
Ops_Log_SQTU_Query SELECT SQTU_Table.Unit, SQTU_Table.Location, SQTU_Table.hiredate FROM SQTU_Table WHERE (((SQTU_Table.hiredate)=Date()));
When run separately the first query returns 2 results and the other 1 result - fine so far. I am now trying to combine the results for display in a report so I have a third query which takes its info from the first two -
SELECT DISTINCTROW Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate FROM Ops_Log_996_Query, Ops_Log_SQTU_Query GROUP BY Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate;
This displays the 2 separate records in the first 3 columns ok but in the last 3 columns the info in record 2 is a repeat of record 1. :confused:
I am creating a database for a hyperthetical car hire company. A customer hires a car from and until a certain date. If a new customer decides to hire a car i want to generate a list of cars that he can choose on depending on the other dates from which other cars are hire from and until. I have created a query that generates all of the cars that the new customer can not use. I have also generated a list of all of the cars, in the database.
I have created a new query takes the numberplates of all the cars and subtracts the numberplates of the cars that are being used.
This is where i hit a problem. For some reason the query is generating the list of number plates twice and then subtracting the numberplates that are being used. This leaves me with with 2 values for every numberplate that can be used and 1value for every numberplate that cannot be used. Can you help me?
Please post your email address so i can send you the zip file as the file is too large to upload. The query that i am having problems with has the title:SEARCH FOR AVAILIABLE CARS.
When run, the user is prompted to enter a team number. If they enter 1, then by my reckoning the query should return all records where [strCurrent_Team] = "Finance".
Code: SELECT Reference, InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField FROM MyTable WHERE (Reference ='ShouldBeOut' OR Reference = 'ShouldBeIn1' OR Reference = 'ShouldBeIn2')
It returns 3 rows, with values in MyField of 0, 23 and 355.
Now I add 1 more where clause to filter on MyField and the query looks like this:
Code: SELECT Reference, InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField FROM MyTable WHERE (Reference ='ShouldBeOut' OR Reference = 'ShouldBeIn1' OR Reference = 'ShouldBeIn2') AND ( InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) > 0 )
So, you would think that it should return 2 values (ShouldBeIn1 and 2), but it doesn't. It only return the value where the Instr returns value of 23. The one with the value of 355 also disappears. Why would that be? Surely Instr does not return a byte?
I have an inventory checklist being done up now. After the item info has been typed in (price, part number.. ), below i put in the transaction info (4 sold today, 2 recieved yesterday..)
I need by report to show the total number of all stocks and how much they are all worth. The report does that, no problem. Only thing is that instead of the end product, Eg: Product X, 5 pieces, $10
it also prints the transactions in the report. Eg: Product X 4 pieces, $8 Product X -1 piece ($2) Product X 2 pieces, $4.
What should I do to make my report -not- print all this useless junk, but just the end product? Thanks
I'm creating an item look-up form with 2 criteria that I wan't to auto-filter as the user types. I'm placing my code in the Change event of the textbox, and only calling the .text for the current textbox (.value or no property at all for the other). The filter works smoothly and as intended until it returns no results in my table, and then it pops me with the error. I've tried forcing focus to the textbox in different places throughout the code, but to no avail. If I use the .value or no property for the current textbox, I'm not getting the CURRENT text, but the old text before it was changed, so the filter doesn't work properly.
Private Sub txtVendor_Change() Me.FilterOn = True Me.Filter = _ "((strSearchVendor Like '*" & Me.txtVendor.Text & "*' AND strSearchItem LIKE '*" & Me.txtPartNum & "*'))" Me.txtVendor.SelStart = Len(Me.txtVendor.Text) End Sub
It usually pops the error on the last line "Len(Me.txtVendor.Text)", but I've had it do it on the Me.Filter line as well (only when debugging and messing with next statement placement).
I wrote some code in Excel to open and run a module in Access. The code executes, but when I check the results I get no records, however if I just step back 1 line and rerun the query, I get records. Alternately, if I run the code twice it will get results the 2nd time.
The OpenCounter opens a Session in the terminal screen then performs a macro that writes to a temporary table.
Here is the code:
Dim db As ADODB.Connection, Rst As ADODB.Recordset Dim Acc As Access.Application Set db = New ADODB.Connection db.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBPath Set Rst = New ADODB.Recordset
[Code] ....
One thing I tried with limited success was to tell the OpenCounter to open and close the table after it was done writing to it, but that seems like a lazy solution and it also doesn't always work.
I have a table providing a list of all members of a local bowling club, with the following fields - MemID, MemName (table is tblMembers)
I also have two more tables providing details of matches we play in a local bowling league. The first table holds the basic match information (called tblMatches) - MatchID, Date, Opponents, ScoreFor, ScoreOpps. I've used a MatchID field as more than 1 match can be played on a single date.
The other table provides details of who played in what match and on what bowling rink and is called tblMatchDetails, with the following fields - MatchID, MemID, Rink (numeric), Result (numeric - 1 for win, 0 for loss). Each match uses different rinks, and not all rinks are used in a match, however, each rink can only be used once in a match.
We have 10 rinks, and I was looking to see if I could get a table to show me the match details along with the rinks in numerical order with who played on them in what match and their result.
For example:
Field Row: Date - Opponents - ScoreFor - ScoreAgainst - Rink 1 Player - Rink 1 Result - Rink 2 player - Rink 2 result - [and son on upto] - Rink 10 player - Rink 10 result. Row 1: 01/01/2005 - Example BC - 4 - 3 - [blank] - [blank] - Fred - 1, etc Row 2: 08/01/2005 - Other BC - 5 - 2 - John - 0 - [blank] - [blank], etc
I produced a query for each rink in turn which looked in tblMatchDetails and provided the following (the queries are called qryRinkOneDets - or whatever rink numebr was used):
MatchID - PlayerID with a criteria field of rink = "1" or whatever number rink it is for.
I then produced a second query providing a link of the matches (tblMatches) and the rink queries (qryRinkOneDets, etc), with the joins between the MatchID fields in tblMatches and the individual queries to provide every record from tblMatches and only those records from qryRinkOneDets where MatchID was equal.
However, the results I got only showed the match details and no info from the Rink query. If I change the join from RIGHT to LEFT, then I get the records from the Rink query but not from the Matches table. If I then do a join where only the MatchID's from the table and query are equal (an INNER join) then I get no records at all.
This is the code that I used to build a query. But some of the data that I thought I removed is still showing up. Any guidance on why? As always all feedback is welcome.
SELECT final.[SSN P ], final.[SSN S ], final.[SP'S SEC Y97], final.[SP'S SEC Y98], final.[SP'S SEC Y99], final.[SP'S SEC Y00], final.NCCD, final.[TC-530], final.[ DOB ], final.[ DOD ], final.[ PRIMARY NAME (ENMOD) ], final.[ PRIMARY NAME CONT'D (ENMOD) ], final.[ STREET (ENMOD) ], final.[ CITY (ENMOD) ], final.ST, final.[ZIP 1], final.ZIP2, final.ZP3, final.ZP3, final.CNLY, final.C, final.YRLR, final.[ PRIOR YEAR NAME (ENMOD) ], final.PNLY, final.P, final.[XREF SSN ], final.[INOLEX XREF CD V], final.[ INOLEX XREF TIN V ], final.[INOLEX XREF CD I ], final.[ INOLEX XREF TIN I], final.[ PRIMARY NAME (IRPTR) ], final.[ PRIMARY NAME CONT'D (IRPTR) ], final.[ STREET (IRPTR) ], final.[ CITY (IRPTR) ], final.ST1, final.[ZIP 11], final.ZIP21, final.ZP31, final.TXPD, final.[#DOCS], final.[#_SUM], final.[WAGES ], final.TX_WTHLD, final.ALLC_TPS, final.INTEREST, final.MTGINTPD, final.POINTSPD, final.PRYRRFND, final.SAV_BOND, final.DIVIDEND, final.PENS_ANN, final.[IRA_CTB ], final.NONEMPCM, final.CPTLGAIN, final.RL_ES_SL, final.GRSSDIST, final.TXBL_AMT, final.FICATXWH, final.TFICAWGS, final.MEDPYMNT, final.TFICATIP, final.FICAMISC, final.DFRDCOMP, final.UNEMPCOM, final.[RENTS ], final.ROYALTES, final.MEDCREWH, final.MEDCR_WG, final.[OR_K1 ], final.GRWINING, final.STDLNAMT, final.[ORD_DIV ], final.ROIRACTB, final.SMPL_CTB, final.[FMV ], final.CD_S_SMP, final.STK_BOND, final.[ADV_EIC ], final.FISH_INC, final.OTHERINC, final.SUB_PMTS, final.GOLD_PAR, final.CROP_INS, final.TXSTTUIT, final.TX_GRANT, final.[AG_SUBS ], final.INTFORFT, final.ORISSDSC, final.BARTERNG, final.PROFLOSS, final.AMTDBTCN, final.PATRONAG, final.MSAGRDIS, final.[TX-PRD], final.[LFRZ-RFRZ], final.MODULE_BALANCE, final.[TC-150], final.TC150_DT, final.[TC150_DLN ], final.RETRCDDT, final.[TC-290], final.[TC-291], final.[TC-300], final.DC, final.[TC-301], final.DC1, final.[TC-420], final.[TC-421], final.DC2, final.[TC-424], final.PRJ, final.[TC-425], final.PRJ1, final.[TC-460], final.TC460_DT, final.[TC-462], final.TC462_DT, final.[COLL-ASG], final.CC, final.[TC-540], final.[TC-590], final.CC1, final.[TC-591], final.CC2, final.[TC-594], final.CC3, final.[TC-598], final.[TC598 DT], final.[TC-599], final.CC4, final.[TC-610], final.[TC610 DT], final.[ TC610 AMT ], final.[TC-611], final.[TC611 DT], final.[ TC611 AMT ], final.[TC-612], final.[TC612 DT], final.[ TC612 AMT ], final.[TC-670], final.[TC670 DT], final.[TC670 AMT ], final.[TC-671], final.[TC671 DT], final.[TC671 AMT ], final.[TC672 DT], final.[TC-672], final.[TC672 AMT ], final.[TC-673], final.[TC673 DT], final.[TC673 AMT ], final.[TC-678], final.[TC678 DT], final.[TC678 AMT ], final.[TC-679], final.[TC679 DT], final.[TC679 AMT ], final.[TC-922], final.[PROCESS CDS], final.[TC-960], final.[TC-961], final.[TC-962], final.[TC-976], final.[TC-977], final.TAXPRD, final.ADJRSN FROM final WHERE (((final.[TC-530])<>"TC-530") AND ((final.[ DOB ])>1929) AND ((final.[ DOD ]) Is Not Null) AND ((final.[TC-150])<>"TC-150") AND ((final.[TC-290])<>"TC-290") AND ((final.[TC-291])<>"TC-291") AND ((final.[TC-300])<>"TC-300") AND ((final.[TC-301])<>"TC-301") AND ((final.[TC-420])<>"TC-420" And (final.[TC-420])<>"TC-420") AND ((final.[TC-421])<>"TC-421" And (final.[TC-421])<>"TC-421") AND ((final.[TC-424])<>"TC-424" And (final.[TC-424])<>"TC-424") AND ((final.[TC-540])<>"TC-540") AND ((final.[TC-590])<>"TC-590") AND ((final.[TC-591])<>"TC-591") AND ((final.[TC-594])<>"TC-594") AND ((final.[TC-599])<>"TC-599") AND ((final.[TC-976])<>"TC-976") AND ((final.[TC-977])<>"TC-977")) OR (((final.[ DOB ])<1987));
I am getting unwanted duplicates due to a joining error, I think.
The goal is to extract a list of employees and their accosiated departments showing a field called [CTD] from qry_employee but there are employees associated with more than one department.. I have a qry_employee liked to another qry (“qry_employee_ctd”) containing the desired field [CTD] joined by [employee_name] but there are employees associated with more than one department. NOTE: Both tables contain the department.
Please let me know if more detail is needs to assist.
Any ideas? I am up for any suggestions and appreciate your help greatly Thanks T
I have a query which contains about 19 fields in it and some of the fields contain parameters. What I'm trying to do is to run the query using the parameters I've set, but at the same time remove fields that are empty (therefore only fields that are populated will be shown). I've tried using the 'is not null' parameter but it seems to interfere with the other parameters that I've set and as a result the query doesn't show any information. Also the field containing the 'is not null' parameter is still being shown. I'm not really sure what else to try!! Any suggestions would be most appreciated!!
I have a table, PURCHASE_ITEMS with 3 fields: ID, TYPE, NAME Another table, ITEM_TYPES with 2 fields: ID, TYPE
TYPE in both tables is a text field and there is a one-to-many relationship between them.
When I run a query on PURCHASE_ITEMS, I can see all 25 records. When I set a criteria for TYPE to one of the types, no records appear, even though there are 5 or 6 of that type.
Can anyone help me with this absurdly simple problem that I can't seem to get my brain around?
I hope this doesn't sound too simple for this forum, I'm only a newbie! I have a select query that will display the recordset that meets the criteria (OK I know that's not a big deal) but...I want to know if, when there are no records that meet the criteria, can I open a form (dialog box maybe) that will say there are no records found instead of showing a blank recordset. Once again I hope this is not so blindingly obvious that I have to start watching my coffee intake
Hi, i'm hoping someone here might be able to help me. I have come to a bit of a dead end with a database application i am working on.
The database is a delivery newspaper management system for a newsagents. Basically i need it to link customers to the paper they wish to receive, organise them into delivery rounds, and produce a bill for each customer based on the newspaper they get delivered.
The problem I am currently having is when it comes to the billing. I have: a table that stores customer details a table that stores newspaper details (inc. price) a table that stores the customerID and then the NewspaperID for each day of the week. (since not all customers get a paper everyday of the week)
If a customer gets two papers then two entries are made in the requirements table under there CustomerID)
Now to fetch the price of the newspaper the customer is down to receive each day and then add these all together is where i have been having trouble.
After many different attempts the solution i am currently using is this. I have a query for each day of the week. Each query take the customerID from the requirements table, then the newspaper price from the table storing newspaper details. The problem here is that the query returns the CustomerID next to every newspaper in the database. I found the solution was this. I added the newspaperId from the newspaper details table and in the criteria stated "[tableRequirements].[NewspaperID]" And this worked perfectly. Untill i added a customer who gets two papers. As far as i can tell the query is returning all the possible combinations of which newspaper the customer should get.
Any help with this would be greatly appreciated - thanks in advance.
I am trying to find an Access solution to handling the MAX 2/3rds (I.E. 2from3 or 8from12 etc) returns from a series of "Scores" of a league table which will have many players, playing in many matches. I have created a crosstab query which delivers the total scores for each player from each game and played around with a make table query and a report output which I have used in excel to deliver the best 2/3rds of the scores. As others will be using the completed data base I would like to keep the solution within Access but I cannot work out how or if this is possible. Below is a limited output from the crosstab query showing the Total of all games played which I wish to turn into best 2/3rds.
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'm trying to assign the result of an SQL query to a variable using VBA in Access. The query returns a value but the variable which it is assigned to has a value of Nothing. Here is the code snippet:
Dim queryReturnID As String queryReturnID = "select dbo_tbl_SupplierReturn.ReturnID from dbo_tbl_SupplierReturn" & _ " where SupplierID = " & lstPOHdr.Column(1) Debug.Print queryReturnID Dim RecordSet1 As DAO.RecordSet Set RecordSet1 = CurrentDb.OpenRecordset(queryReturnID)
I am running a report based on a query. When the query returns zero rows, the count function in the report gives an error. Probably a simple solution but I've searched and can't find an answer. What do I do to stop the report when there is no data?
I looked thru the forums and didn't seems to get what I needed;
I want a count query to give me all fields in "group by" column, and assign 0 to where there are no count for certain fields.
I tried to set Outputallfield to "yes" but got an error message saying that there is a data type mismatch. The query ran just fine when outputallfield was set "no" and gave right results.
I'm trying to execute a query which is looking at data from 2 different tables and most of the data fields in the tables are the same. For instance table 1 has Dept, Res, Period_date, & Available Hrs and table 2 has Dept, Res, Period_date, Project & Forecast Hrs. The results I'm looking for are as follows:
Dept Res Period_date Project Forecast Hrs Available Hrs 11 A 11-02-07 123 XXXXX 11 A 11-02-07 456 XXXXX 11 A 11-02-07 789 XXXXX 11 A 11-02-07 012 XXXXX 11 A 11-02-07 XXXXX
But the "Available Hours" are duplicated in the preceeding 4 rows. Thanks for any advice on this.
i want to write a query that display records for only last date entered in a table and i want to show all the records from last date entries in a table.
This is used for a report for a 28-day period (the query selecting the Start Date and the End Date), and the intention is to show the information grouped by Supervisor, showing the information for as many days as they worked.However, I would like to have the overall 28-day averages of the six fields EExpect thru to IntLossEN shown underneath the detail for each individual. I know I could do this in Access Totals in the page footer if the report was confined to one supervisor, but with up to 20 supervisors I am unsure how to get these totals after the detail for each?
I would also like to have this summary information for each individual presented on a separate page at the end of the report (almost like a league table, if you like), so there is a single page that shows the data for each supervisor in comparison to all their colleagues.
I have a query, and when I execute it, the correct data set is returned. However one field shows "DELETE" as the value for all records.
when I ran it on the SQL server management studio as a test it ran fine. End user does not have access to the tables, so I created a small MS Access application. When I ran the query in MS Access, then connected table dbo_DocRef > Notes shows a value of DELETED.
