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.
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.
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.
Can anyone suggest a method for doing what the title asks. I basically have a single table with several fields. One of the fields is the length of music tracks in seconds. What i want to do is to set criteria so that when a query is run the records to not add up to more than 900 seconds. 1stly) Is this "do-able" using queries or do i need to start implementing sql statements which i have limited experience of? 2ndly) Can anyone recommend a suitable method to do so provided it isn't very complex.
Hello! I'm in the process of creating a report, but am running into a problem. I have a number of clients that include fields such as ID, last name, first name, address, etc. and then their account numbers. Each client has a number of different accounts. The problem is that each client's last name, etc is listed again for each different account they have, so the query results looks like this:
ID Last Name Account # 1 Smith 123-56 1 Smith 4458-66 2 Jones 523-63 2 Jones 4525 2 Jones 5236 3 Roberts 5225-56 4 Johnson 523-55
I want the names and client contact info to appear only once in the report and all of the account #s to show also. Any suggestions would be GREATLY appreciated!
In access I queried my results and now I have to do this last step. I basically have an ID column and a tax fee column. I am trying to remove the duplicated ID numbers but if they are duplicated I need it to add up the tax fee column so I can have one ID with all the tax fees added up together instead of several different ones.
I tried doing the equivalent to a pivot table (works perfectly in excel) but when I tried doing it in access, it did not work.
How to combine two tables in one table such that values are neither duplicated nor missed?i wanted to join Table5 and table6 such that the values of Field1 are all populated in the resulted table but neither duplicated nor overlapped!
I am using Access 2010 32-bit on a Windows 7 64-bit platform.I have a database that I have imported existing data into. For the most part, I have eliminated duplicate entries in my Item Number field.
Obviously I have missed at least one (or possibly more) duplicates. When I try to create a NO DUPLICATES index, I am informed that there are duplicate entries.I am looking for a quick way to generate a list of values that are duplicated so I can address those and correct them.Item number field is a number field of type DOUBLE with 2 decimal places.
I am making a report off of a query. The report is returning values from a Option Group. 1=Yes, 2=No and 3=N/A. The report returns the numeric values and I want the value labels instead. So, I created a column in the query for exp1 which looks like the following:
My table doesn't show duplicate records but when I run a query off theat table every single record has a duplicate record. What did I do wrong or how can I fix this?
I have a Query pulling data from several tables. If there are empty cell with no data in them, they cause the Query to completely skip that record. It does not show up in the Query. Not practical since most of the data int the table will not be entered until some time later.
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.
