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
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 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.
Has anyone ever encoutered a problem where when running a query to return a dataset which includes a memo field, the query only returns part of the info contained in the memo field?
Do queries limit the return of memo field data to 255 characters? I have run other queries on other memo fields in the past, and do not recall ever having this problem.
FYI, there is no indexing, criteria or joins/relationships on the memo field - whilst returning other fields, I purley wanna return whatever is contained in the field.
Having a bit a brain freeze today. I have a field that auotmatically puts a date in when a checkbox is checked I am now trying to add a field which returns the day of the week from this date. Brain now mashed I am sure it's pretty simple but I just can't get it to work
My form uses the city table as a drop down look-up field. In the form it show’s up correctly by showing the City Name only.
The problem is that I have a command button that looks up the city in the form as part of an address lookup for Mapquest, but the value it retrieves is the numeric City_ID and not the City Name. How can I fix it to see the correct column?
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 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".
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.
I use this to record piece counts and weight of items of freight that I pick up in Boston ( all seafood by the way) Anyhow here is the problem I'm having. Anytime I create a report one of my field names (RECIVER) keeps displaying as a unique number. Example, "New Enland Fish Market" display's as "14" which is its auto number assigned to it when I created the Table of recievers.
I have a query[view] where I have six employee numbers, I left join these to an Employee table to retreive the names.
I've created a report that uses this query/view as its source. For some reason my report keeps making all six name fields refer to the same control source Employee.Name and not Employee_1.Name, Employee_2.Name, Employee_3.Name,.... respectively.
In design view everything looks fine but when I switch to report view the switch happens, it displays the same name in all fields. When I switch back to design view all of the fields have changed to the same control source.
why this is happening? My tables are in a SQL server.
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'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'm trying to create a query that will sum the total time of a specific field. It seems to be doing it, however the value is off by by a couple minutes on all my examples.
Table
- BusArrivalTime - BusDepartTime
Query
In my Query I'm making a new field like this. It correctly figures out the difference.
Next I'm displaying that Query information inside of a Report by putting this in the Text Box on the Report. It correctly displays the time in the Hours/Minutes format.
=[BusWaitTime]60 & Format([BusWaitTime] Mod 60,":00")
Lastly, I'm using another Text Box on the Report to Sum the Grand Total of the Wait Time for all my records. Here is what Im putting in the Control Source
=Sum([BusWaitTime])60 & Format([BusWaitTime] Mod 60,":00")
...and it's summing my records, but the value is off by a few minutes and I cant figure out why.
In my example, I have 3 records with times of 3:14, 1:35, and 3:20. It should be totaling a figure of 8:09 but its coming to 8:14 instead.
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.
