I'm a beginner trying to build a simple database for work. I'm trying to track the performance of bonds from month to month, in this case January-April. Each bond has a serial number called Serial_ID, but we wanted to create a record for each month we own the bond, so we created a primary key combining Serial_ID and start date called Serial_Start_Date. So if we own one bond, its January record would have a primary key of "4553123A 2013-01-01" and February would be "4553123A 2013-02-01". I created a form that allows a user to select one individual month's bond and display that month's return. However, I'm struggling to find a way to create a form that allows me to just select the Serial_ID and display the four month's records. Right now there is only one table in the database.
I'm creating a form to count the number of employees with birthdays between 2 dates. There are 2 unbound date fields; Start_Date and End_Date. I have an Employee table with DOB field. I've been stuck on how to get the field to return the correct number of employees that fall within the 2 dates.
All. using 2010. I have a form and a subform. my master and child links are set but when I scroll thru my form; my subform does not change to show the matching records. I do have an unbound search field for the same field as the master and child links. When I type in the search field for a record; the subform does change to that particular record. Do you think that has anything to do with it?
I have two tables, identical in terms of structure (number & names of fields etc.) but different in terms of content (records not necessarily the same, some may be common to both, some may only be present in one or other table)
I want to set up a query to count the number of records which are present in one table (Table A) but not in the other (Table B) using a common identifier field, and put the numerical result in a text box on a form (i.e. use the query as the control source for the text box)
I've managed to come up with the following which provides the list of ID's :
Quote:
SELECT tblA.CommentId FROM tblA LEFT JOIN tblB ON tblA.Id = tblB.Id WHERE tblB.Id Is Null;
Which will come in useful elsewhere but how do I adjust this SQL to only return the number of records? I think I have to use COUNT(*) somehow but I'm not familiar with it and not sure of the syntax?
I have to tables. One named wire and the other named Input. As my title says there is not unique ID for the tables to relate.. the only thing to relate is one ID where is not unique for all the rows
In the Wire Table I have 3 rows using the same ID, lets say 123. In the Input Table I have 1 row using the same ID as above, i.e. 123
I need to select a couple of fields from Wire Table and 1 field from Input Table The only relationship I have is the ID 123.... since the ID in Wire Table is repeated in 3 rows.... I would like to use the same value from the Input Table to be the same for the 3 rows in the other table.. Below is an example:
Wire Table:
ID Group 123 A 123 B 123 C
Input Table ID Description 123 Analog
My wanted result would be
Group Description A Analog B Analog C Analog
I tried using the Left join or Join function, but somehow the resulting table has many duplicates of the rows...such as:
Group Description A Analog A Analog A Analog A Analog A Analog B Analog B Analog B Analog B Analog ...
Any Idea on whats going on and how can i solve this problem????
Hi, I have two tables: tblOutTransmittals and tblTransmittedDocs. A document is sent with a transmittal document which the recipient signs to confirm that they received it. I've created a query that will show all the drawings/documents and the information about the associated transmittals.
What I want to do is show display records with a unique CCNum and preferably the one with the most recent TransmittalDate.
I've attached a screenshot and here is the sql: SELECT tblOutTransmittals.CCNum, tblTransmittedDocs.DrawingNum, tblTransmittedDocs.RevisionNum,tblOutTransmittals. * FROM tblOutTransmittals INNER JOIN tblTransmittedDocs ON tblOutTransmittals.DocID = tblTransmittedDocs.outTransmittalID WHERE tblTransmittedDocs.DrawingNum="32-35554"
I've tried it using the DISTINCT keyword but that would only work if I'm returning one field. I tried Group By as well but couldn't get that to work.
I have txt input [txt choice] which has a number (1-9) and then a query which uses the following formula to define which records are visible linked to a Yes/No field
Code: =IIf([Forms]![frm selection]![txt choice]="4",Yes,Yes or No)
If the choice is "4" then it works fine, but if the choice is anything but 4 it still shows only the Yes items. If i change the code to
I'm a complete novice when it comes to dynamic data so forgive my ignorance. My database contains details of projects in progress. A user can click a link in a list and display a summary of that project (okay so far - I've done that bit) - in the database though there are two tables the second one lists project events/comments (title, date, job number and description) - when something interesting happens during a project we add to the 2nd database a summary of what happened (we made 2nd db because the list of things happening could be one entry or 50 entries) - the tables are links by the job number. On the web site I would like that when a user clicks the link to display the info about a project it all also pulls info from the second db but for the project with the same number.
I've hard coded a similar thing before "select * from tblProducts where category like 'Off Road and Dirt Bikes'" but in this case instead of hard typing 'Off Road and Dirt Bikes' I'd like it to automatically insert the job number of the project being looked at from the first database.
I don't think I've explained this as well as I could've - but if anyone could help I will be really grateful.
I need to write a query that shows all records if any 'L' field starts with D. I have written this, but it's only pulling records if L1 starts with D.
SELECT Item, Description, L1, L2, L3, L4, L5, L6, L7, L8, L9, L10, L11, L12 FROM Table5 WHERE ((L1 LIKE 'D*') OR (L2 LIKE 'D*') OR (L3 LIKE 'D*') OR (L4 LIKE 'D*') OR (L5 LIKE 'D*') OR (L6 LIKE 'D*') OR (L7 LIKE 'D*') OR (L8 LIKE 'D*') OR (L9 LIKE 'D*') OR (L10 LIKE 'D*') OR (L11 LIKE 'D*') OR (L12 LIKE 'D*'));
My access database is used to process vehicle trips. Each shift, a vehicle is assigned a docket number (paper based), and on this docket the driver fills out each trip he/she makes. In a shift (thus on a docket) there may be 5 - 15 trips. I want to create an incrementing number for each trip in a new field - can be created as they are processed or after the fact by looping through the resultant data - whatever is easier.
So in simple terms, return all trips for each docket number then number each trip.
Hi all, I am new to microsoft access. I've got one question that can't solve. I am making database with several items. Under each item, there got different field lists to fill in. So I made several forms for each of them. In main form, I put one combo box to choose the item and one common button. So how can I write the code to open the several different forms,one at a time, by clicking one button according to the value that is chosen from combo box?? thank in advance.
Once I enter records in my form and move to a new record, they won't show up in the form. For some reason there are a few records showing in the form but not all. Unsure what has happened or what I need to do to get the records showing.
If I open a the form called frmhome, I can enter information search on hours completed and even enter new towns. The table that the information gets entered into is called Crisis_support_workers. If you open this table, you will see a lot of information that has been entered. However, if you open the form that is meant to put info into that table, there is only 7 records and most of them are blank records. The form where info is entered is called Crisis_support_workers v3.
Form invoice has a subform that can show max 6 records. Once more records are involved I want to show the last 6 including the new empty record. How to do that?
I have a table with a field that shows the number of pieces that a parcel contains. It looks like this: ParcelID, Pieces, Description
Data example: 1001, 5, Jackets 1002, 10, shoes etc
I need to print labels for each piece that shows to which parcel it belongs. The report will look like: 1001, Total pieces = 5, 1 of 5 pieces, Jackets 1001, Total pieces = 5, 2 of 5 pieces, Jackets 1001, Total pieces = 5, 3 of 5 pieces, Jackets 1001, Total pieces = 5, 4 of 5 pieces, Jackets 1001, Total pieces = 5, 5 of 5 pieces, Jackets
and 1002, Total pieces = 10, 1 of 10 pieces, shoes 1002, Total pieces = 10, 2 of 10 pieces, shoes 1002, Total pieces = 10, 3 of 10 pieces, shoes 1002, Total pieces = 10, 4 of 10 pieces, shoes 1002, Total pieces = 10, 5 of 10 pieces, shoes
If I could populate a table where the number of records equal the value of the pieces column then it's a pretty simple report but I can't figure this out.
I have been able to successfully solve the problem of my pages showing relevant records by using form control. How should I make my form display a new record everytime I open it? So, that when a lame user opens it , he wouldnt change the existing data on an existing record. How to solve this problem? Kindly help. thanks
I created a split form. My main form is just a form, based off of tblBlotter. The subform is based off a query, which is the based off the same tblBlotter. (Not sure if this is proper - maybe I just need the table?)
I have a link master and link child field set to ID. When I open the form "subBlotterHistory", it opens and shows as a new record only. I want it to show ALL records on the query subform and be able to scroll up, down and edit.
On my Class form when assigning students there is a combo box which shows the students names. Once a student is picked in the combo box their name shows up in the subform.
What I would like is a way to NOT show a student in the combo box after they have been selected. Is this possible? Or should I be looking at another way of doing this?
I want a form which allows data entry into a main table but also displays a list of records filtered from the same table. There is also an additional, calculated field displayed with the list.
The filter criteria are a date and a name, a list of which are in a separate table.They are separate fields for first and last name but i would like to filter with both and display them concatenated in the list.
By using a query I have been able to sort the data entered so that all I have left is a list of products that are below their reorder level and the number that needs to be ordered, great so far, this is what I want, but when I try to create a purchase order form as a form/sub form and put in the query I created, there are no products listed, it seems as if I cannot associate the two together, as soon as I link the query for the sub form with the purchase order form.
What I would like to happen is that when I open the purchase order form I can enter the purchase forms details and have all the products that need to be ordered visible in the sub form, the only way that I can get this to half work is to delete the link master and link child fields. If this is the way to go how do I link these products to the purchase order?
I have a form with a subform. I want to use the main form to insert new data and the subform to show all records that are there. One could say that the after inserting a new record with the fields in the form and save it, it should appear in the subform datasheet view.
I have a continuous subform. Because the records are often many, the user needs to scroll down the page.
How do I have a text box in each row that shows Record x of Total Records?
I tried setting a text box control source = [currentrecord] in the Detail area of the subform but it didn't work . It did weird things because I suspect the control is trying to show the record that has the focus or something
