Access 2000
tbl_TESTS (lists all active employees for a company)
tbl_RANDOMS (table that lists which employee needs to take a test)
[EmpSS] is a social security field in both tables to connect them. I need a form that will allow me to show all active employees from tbl_TESTS and then select the option for a test from the tbl_RANDOMS.
tbl_RANDOMS
EmpSS
1Q07
2Q07
3Q07
4Q07
tbl_TESTS
CompName
NameFirst
NameLast
EmpSS
EmpInPool (yes/no check box for active employees)
I want a form that lists all of the fields above.
Show all the active employees
Then if I type something into one of the four fields for tbl_RANDOMS it adds a new record to the table.
I have added a simple DB for an example. When the form opens select Wal-Mart from the drop down. You will see the 8 people they have as current employees. Now it will not allow me to select one of the drop downs for any of the quarterly stats. Thanks.
My goal is to create a form that allows me easily edit what State each City is assigned to, and edit what Country each State is assigned to. I have 3 tables:
tbCities CityID (Primary Key) StateID (Foreign Key from tbStates) City
tbStates StateID (Primary Key) CountryID (Foreign Key from tbCountries) State
tbCountries CountryID (Primary Key) Country
For right now, I want to focus on just States and Cities... so I also have this query: quCity_to_State Field: City Table: tbCities
Field: State Table: tbStates
The join type for the relationship between tbCities and tbStates is: Include ALL records from 'tbCities' and only those records from 'tbStates' where the joined fields are equal. That way I can see all the cities and the state they are assigned to.
However, when I view the query in datasheet view and try to type anything into the State field of this query, I keep getting this error: "Cannot enter value into blank field on 'one' side of outer join". I figured the source of the form should be this query instead of the tables directly, but if I can't input data into the query then I wont be able to input data into the form.
I think I have just finished designing my database and I tried to test it and I couldn't enter any new records as it says "Can't enter data into blank field on "one" side of outer join" whenever I try and enter info and I don't know much SQL to work out what has happened. It probably causes this too but I also cannot select check boxes.
The form where I try and enter the info is called Crisis_support_workers v3. I have attached my database so you can look at what I have done.
So I'm trying to manipulate Access to create a Directory for my church. I'm trying to get a report to show the church staff, which I was able to do, but I was wondering, is it possible to get the records to show side by side instead of one on top of the other?
I included a picture of the design view showing what I would like to see. Excuse the way the numbers are written, it's hard to write with a mouse.
I have split my database, the data is in a DB on the server and the forms, reports, etc is on the client desktop. My question is "Is there an advantage to having all of my combo box queries (Lookups) on the server side (defined in the table as a combo lookup) or should I put the all on the form so that they reside in client side DB.
Form with three sections. I have three queries selecting different set of set in a table, I would like to show all three in a form side by side. How can I do this? I use form wizard bit it only uses one query as a source.
I want to Delete only FK on the many side first and the record on the one side by one click of a button. I wrote some code which sometimes works and sometimes it does not!!
I wonder if any one have a better idea or doing this please?
Private Sub Delete_Click()
Dim db As DAO.Database, rs As DAO.Recordset Dim n As Integer, i As Integer Dim vStart As Integer Dim vEnd As Integer Dim vSite As Integer Dim vRCCID As Integer
vSite = Forms![frmSite].Form![SiteID] vRCCID = Forms![frmSite]![Roads Construction Consent].Form![RCCID] vStart = Me.PhaseStart - 1 vEnd = Me.PhaseEnd + 1
Set db = CurrentDb Set rs = db.OpenRecordset("tblPhase") rs.MoveLast n = rs.RecordCount rs.MoveFirst If n > 0 Then For i = 1 To n If rs![SiteID] = vSite Then If rs![PhaseNumber] > vStart And rs![PhaseNumber] < vEnd Then rs.Edit rs![RCCID] = Null rs.Update
End If End If rs.MoveNext Next i End If rs.Close db.Close Set db = Nothing Set rs = Nothing
'/////////////////////////////////////////////
DoCmd.RunSQL "DELETE RCCID FROM tblRCC WHERE RCCID = " & vRCCID & ""
'////////////////////////////////////////////// End Sub
tblRCC is the one side of the relationship and tblPhase is the many side.
I have a report with some text boxes on it. Sometimes the text in these boxes can be a very long string of characters (maybe a path to some folder). In this case I do not want the text box to grow. I just want the report to show me the right-hand side of this path and cut off the left. How do I do this when still aligning my text to the left?
I'm an Oracle guy, not an access guy, so I need some help please. I have 2 questions at least.
I have this query:
SELECT tOrders.ID, tCustomers.[First Name], tCustomers.[Last Name], t_OrderDetails.Product_ID, tProducts.[Prod Desc], tProducts.Price, t_OrderDetails.Qty FROM tYears INNER JOIN (tProducts INNER JOIN ((tMonths INNER JOIN (tCustomers INNER JOIN tOrders ON tCustomers.ID = tOrders.Customer_ID) ON tMonths.ID = tOrders.Month_ID) INNER JOIN t_OrderDetails ON (tOrders.ID = t_OrderDetails.ID) AND (tOrders.Month_ID = t_OrderDetails.Month_ID) AND (tOrders.Year_ID = t_OrderDetails.Year_ID)) ON (tProducts.ID = t_OrderDetails.Product_ID) AND (tProducts.Month_ID = t_OrderDetails.Month_ID) AND (tProducts.Year_ID = t_OrderDetails.Year_ID)) ON tYears.ID = tOrders.Year_ID WHERE (((tYears.Year)=[Forms]![pYEAR-MONTH-rpt]![Year]) AND ((tMonths.Month)=[Forms]![pYEAR-MONTH-rpt]![Month]));
And I want every ROW in the tProducts table to show up for each record in t_OrderDetails. The Orders Table is tOrders and each detail row is in 't_OrderDetails'. I went into design view and changed the links from t_orderdetails to tproducts as follows in this screenshot:
Anyway, in Oracle, I would just simply add (+) to each join column in tProducts but here the message states I must create a subquery and then include that query into the main query. How would I go about doing that?
Any ideas at all would be much appreciated. I'm doing a project for our church for free, and Access is all we have available to use. I've got too far now to turn back.
2nd Question is:
My ultimate goal, is to get this to work, and then to put the totals of each product in tProducts into it's own COLUMN on a report showing how much of something each customer ordered. There are only 4 to 8 products each month, so I hope to see something like:
Box of Food Special #1 Special #2 Special #3 George Jones 1 0 2 3 Dirk Smith 3 2 0 0 Tyler Jones 1 0 3 1
We need the sums to print in columns, not rows. I'm trying to get a loading manifest (for lack of better words) so the volunteers can see how much of each product the customer ordered. There will never (knock on wood) be more than 4 to 8 products ever.
Any ideas on how to do that?
Thanks for any help at all. I really appreciate it.
I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.
I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.
I have Table 1 with one field called Field 1 - values A, B, C Table 2 as follows
Field 1.....Field 2.......Field 3 A.............100 C.............200..........XXX
I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX
My SQL is SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3 FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1 AND Table2.Field3="XXX";
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code: SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID) INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID ) ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
I have two queries, property info (one) and budgets v spend (many). I'm trying to work out what the gross price is, property cost plus budget or spend (which ever is higher). Got that working no problem.
However if there is no budget or spend I just need to show the property cost but I'm just getting Error#. I've tried using the nz function but that's not working.
I have data which has a one-to-many relationship and I would like to display in a form (or webpage) both the one and the many side of the relationship and to display a count of the number of records on the many side which correspond to each individual record on the one side.
I attach a zipped .mdb file containing dummy data which includes a pair of linked forms showing where I've got to so far with implementing the idea. If the Words form is opened first, the linked form, Associations can be opened by clicking on the Associations button.
(Incidentally, I need to do something to control where and at what size the two forms open on screen. I think I've seen from a post on this forum that what I need is DoCmd.MoveSize.)
The problem I'm hoping someone will help me solve is how to get the count of the records on the many side to appear only once and, preferably in the Words form, not the Associations form. If you view the forms in the attachment, you will see that at the moment the count appears once for each item on the many side in the Associations form.
I'm a beginner and am unsure if the solution is very simple, perhaps involving referencing a hidden control in the Associations form with a path to that control comprising various identifiers joined with ! and or . , or perhaps using a simple snippet of Visual Basic, or whether I'm asking for help with something which is actually difficult.
I am new to Access programming. I read in some web page on googling that Access is for developing Desktop database. Books inform that Access projects can connect to Server database.
I would like to know if Access can also be use for Server side programming.
I would like to make a query that combines the StoreNumber into one field CombineStoreNumbers separated by commas as follows...
LogNumber: 1 CombineStoreNumbers: 2, 3, 4
I tried the following crosstab query, but it's not quite right. TRANSFORM StoreNumber SELECT LogNumber FROM tblWorkOrderStoresFinal WHERE (((LogNumber)=1)) GROUP BY LogNumber PIVOT StoreNumber;
I would like to create an application with a left side tree menu. Each leaf of the tree should open a form or launch a procedure. The branches could expand or collapse. Nodes could have pics.
There must be a couple of VAB examples. Would you recommend me some of the best ones ?
Hi all, I am programming a database that contains approximately 3700 records stored in a relational table structure.
When I split the database and moved the tables to the server side, my performance worsened drastically: a query that runs in under 5 seconds when the tables are on the client side takes over 45 seconds in the split database (with tables on the server side).
I've tried a variety of solutions (including changing the subdatasheet name property to [None] in the tables, creating an MDE file from the MDB file), but they haven't helped much.
Any suggestions about how to speed performance are greatly appreciated! Thanks so much!
In a query I am trying to return all dates (birth dates) within seven days either side of the current date. The properties of the field and the query are set to Medium Date and the criteria I am using in the query is:
Between Date()-7 And Date()+7
and returns nothing.
I have tried several options suggested in other threads without success. Access 2002 and Australian date format - 14-Sep-07
Hi! I have a table named Employee Records, with a primary key "ERName". I use this to link to a table named Certification Records, with ERName as the link in both tables. My problem is this:
When I add a new record to Employee Records table, the name is not thrown in to a new record in Certification Records. This is a problem, because in my form that uses a query that includes both tables, when I add a certification record, it doesn't populate the name field in Employee Records table. I don't know where I went wrong, but I feel stupid because this is so basic and I should have caught it earlier... any ideas, please? Thanks!
You know how you get the form header/detail/footer sections on a form. Well, how can you make text appear down the side (ie so that it overlaps all of the header/detail/footer sections).
Take a look at my drawing, it will explain it better...
I have developed an access database, and prepared its run time exe file. My question is how to develop the database so that the user can regularly backup the database?
I am trying to create a warehouse database in acees 2007 and 2010 that can track goods that come in.i have two types of goods Specials which is not on my current stock list and Current stock of which i have a excel sheet of around 32000+ items.I have 2 warehouse to put the stock in with over 100 locations.I am trying to track the stock that comes in by saying it is Special or Current stock if Special and i enter the data it should add it to my Stock sheet if current I can choose it from the Current Stock list; allocate it to a location .
Lastly i need to be able to move the stock either from one location to another in the warehouses or to a customer on a orderI have created a few tables and tried to link it as best i can after reading 100's of posts and access for dummies
tblProduct ProductPK-Autonumber ProductCode - Text Description - Text Dept - Text
tblLocation LocationPK - AutoNumber Location - Text
lnktblPtoductLocation LinkPK -Autonumber ProductFK - Number(long integer) LocationFK - Number(Long Integer) Qty - Number PoNumber - Text DateIn - DateTime
i set this up as a many to many relation and that is as far as i got.when i try and enter a location for a product to test it say i cant update location field and things like i cant enter data on the one side of the many to many relation.
Hello the problem I am having is that when I imported data from excel, it somehow placed carraige return at the end of the text box ( this is what I believe), this is a problem because in that field is a drop down box and it complains about my value not being valid. I press the del key behind the value and it doesnt complain. Can anyone help me?
