I'm trying to make a sub form that displays the hours of an employee selected in a listbox. I've got most of it working but having a bit of an issue.
The info for thre query is in 3 tables:
tblStaff (name etc)
tblShifts (start and end times for days that this employee works)
tblDays (a list of days names so I can use numbers elsewhere)
My query looks like this:
Code:
SELECT tblDays.dayName, IIf(Nz([startTime],"")="","NWD",[startTime]) AS start, IIf(Nz([endTime],"")="","NWD",[endTime]) AS [end], tblStaff.staffName
FROM tblDays LEFT JOIN (tblStaff RIGHT JOIN tblShifts ON tblStaff.staffPK = tblShifts.staffFK) ON tblDays.dayPK = tblShifts.workingDay
WHERE (((tblStaff.staffName)=[Forms]![frmMain]![lst_myTeam] Or (tblStaff.staffName) Is Null));
This worked fine with a single user and some test data - it correctly displayed all days of the week, with start/end times on Mon and Tues where I had entered shift information, and "NWD" against all other days.
However, when I add a couple more employees to the mix it shows the correct info for the first employee, but anybody else it will only display days where person 1 doesn't have any hours. I haven't entered any hours for the new employees, but the query should still display Mon-Sun with NWD in every column. It shows Wed-Sun but Mon and Tues are missing.
I've tried different join types but they all come back with "ambiguous joins" error when I try to run.
I am exporting data from Access to excel, once all the data is exported into multiple sheets. I have one Master Sheet which has formula / reference to other sheets. I want to hide those rows which do not have data but contain formulaes referencing back to another sheet.
How to hide those records has formula but do not have data.
I have a query which is grouped by year, facility name, and billing type and needs to display several columns of aggregate values for each of these, e.g. count of cases, sum of cases reviewed, average length of stay, etc. It would look like this:
2003 ABC Hospital Billed DRG 123 22 6.5 2003 ABC Hospital Billed Per Diem 23 0 4.2 2003 DEF Hospital Billed DRG 456 120 12.3 2003 DEF Hospital Billed Per Diem 56 10 2.1
However, if a facility does not have any Per Diem billings for example, how can I generate a row for it anyway? I cannot use a crosstab since I have multiple different values to display. I tried the following union query:
SELECT YR,FacilityName,BilledDRG,NumberOfCases,AverageLen gthOfStay,CasesReviewed,CasesDenied,AvgOflos,[Billing Eligibility] FROM qryDistHIVCasesStatsDAC UNION SELECT YR,FacilityName,BilledDRG,0,0,0,0,0,null FROM qryDistHIVCasesStatsDACDRG UNION SELECT YR,FacilityName,BilledDRG,0,0,0,0,0,null FROM qryDistHIVCasesStatsDACPD ORDER BY YR, FacilityName, BilledDRG;
where qryDistHIVCasesStatsDAC is the original query,
qryDistHIVCasesStatsDACDRG shows the year, facility name, and "Billed DRG" qryDistHIVCasesStatsDACPD shows the year, facility name, and "Billed Per Diem"
but then I would get e.g. 2003 ABC Hospital Billed DRG 123 22 6.5 2003 ABC Hospital Billed DRG 0 0 0 2003 ABC Hospital Billed Per Diem 23 0 4.2 2003 ABC Hospital Billed Per Diem 0 0 0 2003 GHI Hospital Billed DRG 456 120 12.3 2003 GHI Hospital Billed DRG 0 0 0 2003 GHI Hospital Billed Per Diem 0 0 0
what I want is: 2003 ABC Hospital Billed DRG 123 22 6.5 2003 ABC Hospital Billed Per Diem 23 0 4.2 2003 GHI Hospital Billed DRG 456 120 12.3 2003 GHI Hospital Billed Per Diem 0 0 0
In other words, for each year, each facility should have a row "Billed DRG" and a row "Billed Per Diem" and only one row of each.
Thanks for your assistance. I hope I have explained this adequately.
I need query to display ALL the rows from Table3 and show columns of Owner_LastName and Tenant_Lastname. However, I want the rows in Table3, that do not have the value for TenantID to still appear, just with Tenant_LastName being left blank.
I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.
Tables: Days with 7 records Hours with 24 records Workers with as many people that sign up to work the different hours Schedule signifying the worker, day, and hour which are signed up.
As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:
What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:
I have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.
Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.
I have a cross tab query that displays data by customer (rows) and MONTHS (columns).
However I need the columns to be the 12 months of the year 1 to 12.
However, if the selected data for a particular customer does not have any records in a month then I get an error in the report as the cross tab query only selects the months with data.
How do I get the report to show 0 or a blank in these columns
I have a problem, so I am trying to append data from excel which works fine but I want to append it to the next empty available row in the datasheet view. At the moment it is just appending it to the top, first line. Im using MS Access 2007-2010.
I currently have query that looks at 2 tables that hold financial information. I am querying the data to do some adding and subtracting based off a financial class of a facility. The issue I am having is that one table may have a financial code that the other does not have but I still need to show that financial class for the facility.
My looks as follows
FROM [CashValue Link] INNER JOIN TCashValue ON ([CashValue Link].FINANCIAL = [TCashValue].FINANCIAL) and ([CashValue Link].Date1 = TCashValue.Date1) AND ([CashValue Link].FACILITY = TCashValue.FACILITY) AND ([CashValue Link].CLT = TCashValue.CLT)
I have a master table that holds all of my data. The table details what qualifications someone is holding.I would like a query that would enable me to produce a list of people who DO NOT hold a qualification.
In the above example student 1234 has a missing record in year 2004 which supposed to be Term2 in the previous year (i.e. 003) and Term2 supposed to be Term1 value in the following year (i.e. 004). Similarly for student 3311
I have 2 tables: one for repairs and the other for the billing for those repairs. There is a foreign key(record_num) in the billing table to match the primary key(prikey) in the repairs table. This works fine as long as the unit repair has been completed.
Now an employee wants to see records even if they are not completed and wants the rate to be $0.00 if the unit has not been completed. But by this method there is no record in the billing table.
My problem is if I have the 2 tables joined then I only see records that match both tables. Here is my SQL for the query:
SELECT DISTINCTROW tbl_module_repairs.end_user, tbl_module_repairs.pickup_date, tbl_module_repairs.complete_date, IIf([pickup_entity]="Storm","APS Storm","APS Field Tech") AS [Repair Pickup], tbl_module_repairs.mfg_part_num, tbl_module_repairs.manufacturer, tbl_module_repairs.module_type, tbl_module_repairs.incoming_module_sn,
I have an old table from a database with 2000+ rows deleted and it had no primary key (but it had a number column).
I am splitting another old table which relies on the number column of that old table but I am importing it into a new table with an autonumber primary key which the split table will rely on.
Thing is I need the number and the autonumber to match up accross all records
1 has to be 1 2 has to be 2 and so on
is there a way to "fill" in rows with the missing numbers? i.e. if I have 1,2,3 and 2 is missing the method adds a row "2" to the table.
I am using Access 2007 and we are importing an Excel spreadsheet that is received from our customer on a regular basis.When I open the Access table I see over 9,900 empty rows at the start. Following this is the actual data from the spreadsheet.Is there an import setting somewhere that would eliminate all these blank rows?
I am designing a bead ornament database for my mom to track inventory of beads, inventory of finished ornaments and cost/pricing. I have a table that joins the Item and bead part with the quantity of each bead part needed, it has the following fields: ItemMatlID, ItemID, BeadPartID, QtySo I have multiple rows of ItemIDs for all the BeadPartID & Qtys.Now I need a query with 1 line per Item and all BeadParts and their quantities. However, I need the BeadPart to be a row data and not a column heading. So a crosstab is out, I believe since it wants to make the BeadPart the column heading and not data in the query.
Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.
Questions How can i in Q1 retrive only numeric data from F1 and display that data i a field? How can i in Q1 retrive only text from F1 and display that data i a field?
Wondering if anyone can help here? I am currently working on a form that needs to display data from 3 unique queries - a crosstab query and two standard queries (one has line by line data and another summary data) that do not have a common link.
What is the best way to do this? Would it be possible to display the results of each of these queries in a single form by creating 2 subforms from 2 of the queries and placing them in the form of the 3rd?
Any suggestions on how to combine the data from the 3 queries into the 1 form would be much appreciated!!!
I have a table where I record attendance. In the table they select the type of training in via a check box "Training, Drill or Other". I created a query to show how many times someone has attended the Drills in each quarter (counts the check box). I am wanting to be able to specify which year is displayed as atm it is showing all te quarters for years. The SQL is below.
From this I have a report where it displays the data and have conditional formatted it so that if they have not attended more than 4 times in one quarter the "cell" turns red - the only problem is is that some of the cells have no data in it. How can I get the query to display 0 if there is no data?
QUERY SQL: TRANSFORM Sum(Abs([ATTENDANCE 2011].DRILL)) AS SumOfDRILL SELECT [MEMBERS LIST].[NUMBER/NAME], Sum(Abs(Nz([ATTENDANCE 2011].DRILL,0))) AS [Total Of DRILL] FROM [MEMBERS LIST] INNER JOIN [ATTENDANCE 2011] ON [MEMBERS LIST].ID = [ATTENDANCE 2011].ATTENDED.Value GROUP BY [MEMBERS LIST].[NUMBER/NAME] PIVOT "Qtr " & Format([DATE],"yyyy/q");
I currently have two different excel spreadsheets linked to access 2003. The first few columns are very similar with similar headings. What i have now is a form displaying the data from one spread sheet (Engine Status), and another form displaying the data from the other (Engine Health).
As I am very new to access is there a way that i can link the two spreadsheets together and display all the data on one single form. I would like a combo box to find the required serial number (tail number) and once the serial number is selected the data from both spreadsheets will be displayed.
The spreadsheets i have to work with are of set standard for ease to input data from numerous other sources. I have attached examples of the two spreadsheets with the headings that are the same between the two although i have attached these to a single work book in reality they are seperate work books.
What i would like is for a combo box to display the Tail number then have single boxes to display core serial numbers across the form in order of position, then all the other data associated with that core serial number displayed in seperate boxes below the core serial number. I hope this is possible as it will prevent a lot of toggling between forms.
I am trying to get this data to display in a matrix format.How can I get it to display multiple entries in there, such as Fiona and Chloe in the FR box?