I currently have a DB that has a field in a table that records which shift (A, B, or C) that some data was collected on. I am trying to write a Query in order to filter the results so that either the data from shifts A,B, or C show up, or if "All Shifts" is selected, then all the data shows up. I have another table set up that records the shift selection from a form. I was trying to use an Iif statement but it does not seem to be working for the "All Shifts" option. Selecting "Shift A" or "Shift B" etc. works fine.
**[ShiftSelect] containts the users selection for which shift they want to view.
I have also tried this with a wildcard statement (Like "*") instead of "Shift [ABC]", to see if I could get any data to show but I still get nothing when "All Shifts" is selected!
Let me know if you can see the problem, or know another way to go about this query!
I have a Membership List Table with a calculated field entitled Member Name.
The second table entitled Groups contains only 46 records each of which has up to 30 Fields also containing member names.
I Have written a query to establish which of the members from the single field of the first table do NOT featured in any of the fields and records of the second table. In the Query Design View I have listed 'Member name' from Table 1 followed by all the relevant member fields from Table 2, carrying the criteria 'Is Null'.
When I run the query, it merely lists all the members from Table 1.
I am have some difficulies in the following query:
The result i need is Summary5 ResultG/001, G/0015 (Room) and soo on
Field Names Are: Room & Summary5
I realy am not sure as how to do this, if it help the reason why i need to do this is for a report that will show "Asbestos Found In Rooms ...."(Summary5:Asbestos Found In Rooms" "&[Room]) is what i tried but this does not show it right, any ideas.
Alastair
**************************** Resolved With Thanks to raskew (Bob) & Matt Greatorex*****************
As I usually concentrate on building the tables and fields in my initial stage, and do some experimenting to analyze the relationship.
However, when I'm done, I find it quite a hassle to go manually through the tables to add description to each field for documenting.
What I would like to do is create a query that will list all fields and its description. Now, I have found codes to retrieve either table or fields properties, including the description property, but am kind of stumped on how I can make it a SQL statement so I can create a temporary query, which will be pretty be one time thing, that can read all fields' description and allow me to type in it, save it.
If that's not possible, I suppose I could write code, using Allen Browne's example, that would loop through the fields' description, debugging to the immediate window and prompting me if I would like to add something to the blank description. However, this isn't exactly greatest as I need to see all fields so I can be sure I'm giving good descriptions.
I figured someone may have had this same problem and maybe came up with a solution?
I have a table in access which is updated weekly; I need to create two tables from this updated table.
1st table will consist of all the new entries for the current week
2nd table will consist of all the entries from the previous week - an amalgamation of all the entries which are not from the "current week" (table) For example; the table below shows the two entries from last week.
ID Name
1 Adam
2 Ben
This week I have three new entries New entries
ID Name
3 Charles
4 Richard
So when I run the same query next week I will get something like this.
Old Entries ID Name
1 Adam
2 Ben
3 Charles
4 Richard
[code]....
How do I get a query /queries which divides up the weeks new entries and also all the old entries.
I have a table with a list of lectures and the dates and times they are running. I then have a related table which lists the feedback marks and comments for each lecture. I want to enter them via a form and have set up a form with the the lectures and then a subform for entering the feedback. This all works fine. However I would like to be able to have the lectures listed by date order and wondered what the best way is to make this work so that the user can simply scroll through each lecture on the form and it automatically goes through the list by date.
Having recently correlated the impossible pile of photos that I have on CD, including a lot of re-naming etc... I have now managed to get them down onto DVD.
I there an easy way to import a directory listing into an access database other than inputing the lot by hand? I don't wan't to add thumbnails just the file name and the directory it's in. I can embelish other details later via ASP, just need the bulk of the work done easily.
I have some experience of database design but not huge amounts of programming, other than the ASP side of things so if anyone has a complex answer please go gently with me!!!
I have a Form named frmClearances that lists the name, age, Club Clearing From, Club Clearing To and the Date of Clearance... this is used for my local sporting Club.
I have created reports that separate names into "Clubs Clearing From" so that each Club is aware of who has gone. The Reports are named... clrSportingClubName
I have searched this Forum and all over as well, including Tutorials and I cannot understand how I can make a list box that contains the names of the reports so that they can be selected individually when required for each Club.
I copied the SQL for each query and placed it in the RecordSource of each Club Report so that I did not have numerous queries.
I have even copied code from other list boxes to then changed a couple of things but that has been to no avail.
Could somebody please explain to me how I do this? Thank-you very much in advance for any assistance.
I have a report that i am trying to list in the header section, a listing of all the names for a particular field, if it is in my report. I will use these names to send the report to.
So I am using 2003. On the report I have created I have this one entry that exists for all my records, it is a Name, and at the bottom of the report I am trying to create a summary that will list each of those names. I already have a count going, so I thought maybe I could tap that, but not every record has a name listed. I am farely inept at coding.
What I want is this: (Institution 1) Accrediting Institution: ACICS (Institution 2) Accrediting Institution: ACCST (The part above is Done, what I need in Below)
So I have a textbox on a form that contain values. I want to convert it to a ComboBox. Since a lot of these values are the same in a lot of records (for example, "Inventor"), I want the entered values to appear in the ComboBox dropdown so they can just be selected instead of typed in. I converted the textbox to a Combobox by right-clicking on the textbox => Change To => ComboBox. It changed successfully, but I want it to list the values in the dropdown, but it doesn't.
I have a crazy question. Is there a way to write a query that would display data rows with common ID's as if they were columns in a query? It's for visually representing row data as if they were one record in a list. Basically, I would like to take the following data...
fldID fldDetail fldValue 1 Shape Round 1 Color Blue 2 Shape Square 2 Color Red
And display it in a recordsource like each is one record: ID Shape Color 1 Round Blue 2 Square Red
Please refer to the pic 'figure1' to review my question. Thanks.
I need to use a combo box to select the employee name and show the following Ref No. by using the list box. is it possible to do that? If can make it, how bout i need to delete the selected record, any idea for that? Thank you.
Our software was purchased with a volume license, so I want to make a combo box with all of our volume license purchases and when the person selects the volume license number all of the PCs that have software purchased with that volume license are displayed.Below is my code
Private Sub cboVolumeLicensing_AfterUpdate() 'Moves to Volume Licensing field and 'finds records where "Volume Licensing" matches whatever is selected in the combo box DoCmd.ShowAllRecords Me!cboVolumeLicensing.SetFocus DoCmd.FindRecord Me!cboVolumeLicensing 'Set value of combo box equal to an empty string End Sub
The Query is Volume License Query and it is composed of Main Table and Volume Licensing Table connected by serial number on the main table and volume license on the volume licensing table..The combo box is volumeLicensing .And the form I am trying to do this in is called Volume License Form
My aim is to chart the career history of many people in my DB and my intention is to achieve this with two "Main" tables with many smaller tables linked to it.
Table 1 will contain the generic data about the individual, employee number, name, DOB, etc etc (there are many more fields) and Table 2 would hold their career history. Where they have worked, when, etc.
When viewing Table 1, I see all data, including a sub table showing career history, which is great. What I would like to do however, is have a query that will return all data from table 1 and Table 2 that refer to the employee number.
Attempts that I have made thus far, mean I have to enter the employee number twice (I would prefer to enter this only once) and then it gives me many pages (in report view) each page has all data from table 1, and one entry from table 2.
I would like to see all data from table 1 and then all data from table two, listed.
I am after creating a list box that will display some of the tables within my database - there are between 10 and 15 tables and the names all follow the same pattern (They all start "tblD10").
Hi, newbie here to this forum, been looking about for ages to try and find an access based forum.
I am not a great coder, have not really done it, nor remotely understand it, but I have still managed to write a database in access for the office.
We run a residential care home, where we had 5 databases (MS Works) which held different information of the residents within the home but duplicated name, dob, etc. I have successfully writen a access database which works well. But now I want to start pushing it a degree further if I can.
We took delivery of a HP officejet all in one printer, and it has a scanner with document feeder which we scan letters in so we can share them with head office, but what I would really like to do, is scan letters in relating to a specific client, and be able to save the file name as a link, so an other user on an other PC can open the document on their PC. Basically, I want to avoid us going to the filing cabinet to refer to a letter which was written to us.
I am using XP Home, with Office 2003 Pro, and Acrobat 8 Pro.
Any ideas where to even start looking?? I did do a search, but have no idea how to start.
I want to list all the records of a particular field from all tables in my database. The field has the same name in all the tables. Ideally I would like one long list of the records. How can I do this?
I'm trying to design a report which lists jobs that are overdue. I've got a field called [ExpectedCompletionDate] and a field called [Status]. The Status field is polulated by a combo box, which has the following options
Started In Progress Complete
My issue is that i'd like the report to list all the jobs which have exceeded the expected completion date and whose status is not listed as 'Complete'.I'm using the following code in the underlying query
Overdue: IIf([ExpectedCompletionDate]<Date(),IIf([Status] Is Not "Complete","Overdue"))
I was just wondering if its possible, i'm sure it is....... I've got a query to pull out invoices for a certain Project it brings up all project invoices and puts it into a report which works fine...... what i want to do tho is perform the same query but after i type in project code in criteria [] i then want to type peoples name or ID's that i want to print as some will be sent at different time intervals and i don't want to have to sort through invoice's to then send? is it possible? any one got any examples?
I have 2 tables... 1 is customer info (name, address, etc...), and the second is shipments (ShipToCustomerName, ShipmentDate, etc...).
Now, I wish to query the shipments table to get ONLY the LATEST shipment information for EACH customer.
If customer A has a shipment in Jan 1, Feb 2, and March 3... while customer B has a shipment in April 4 and May 5. then the result of the query should be
customer A - March 3 customer B - May 5
Is there a simple way to construct such a query?!?
Very new to access, I need an easy way to compare 2 tables with a common field (the name field) and list just the records that appear in the second table but not the first (primary) table.
