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.
Access 2010
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?
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.
View 2 Replies View RelatedI have two tables which I pull records from. One table has a list of sections. The other table is a list of chapters.
One chapter has many sections.
I have the sections table populated, and want to show all of the sections, even if they are not currently assigned to a chapter.
Currently only the sections which have a chapter show up in my query.
I know this is really simple to do ..... I'm just drawing a blank on it!
Any help would be appreciated.
Gordon
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.
View 7 Replies View RelatedHi,
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.
Any help will be very much appreciated.
B
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 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.
Any ideas?
Thanks
Hi folks,
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.
- Krish:confused:
Hello,
i am trying to join the results of a many-side of a query into one result field separated by commas...
Table: tblWorkOrderStoresFinal
Row 1: LogNumber: 1StoreNumber: 2
Row 2: LogNumber: 1StoreNumber: 3
Row 3: LogNumber: 1StoreNumber: 4
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;
Any ideas? Hope this makes sense.
Thanks, Casey
Hi,
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 ?
Thanks
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.
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!
Hi
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
Any advice would be appreciated.
Tks
Allan
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!
KellyJo
Hi Guys,
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...
Raw data
aaa, bb, ccc, dd
or
aa, bbb, cc
I want field1 to be
aaa, bb, ccc
or
aa, bbb
This drops everything to the right of the 1st comma
field1 would = dd
or cc
Code:
Me.field1 = (Right([field1], (InStr(1, [field1], ",") - 1)))
This drops everything from the 1st comma on the left to the end.
field 1 would = aaa
or aa
Code:
Me.field1 = (Left([field1], (InStr(1, [field1], ",") - 1)))
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?
View 1 Replies View RelatedI have a text such as "Arun-Halyal-12". I need to extract "12" from the text in MS Access query. What formula i have to add in query to get "12".
View 3 Replies View RelatedI 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?
View 1 Replies View RelatedI have a subform that�s in datasheet view, is there a way to align the vertical scroll bar to the left side?
Using:
Win 7
Access 2010
I have a form that stores information about attendees for a given class. In this particular instance, I only store one address, one contact information both which are optional. I've used subforms, but that has bought problems (one notable problem is that one field from tblAddress is required for *any* attendee, which is county they resides in, whether they give out an address or not. At this point my VBA codes to work around this is simply too buggy and a major hassle.
Therefore, I am now considering two possibilities: 1) make a unbound form and do everything manually without any subforms or 2) create an updatable query somehow that can pull together the needed information. The latter, I'm not sure if that is feasible, so I'm asking you about that.
Address and Contact Information are kept as a many side tables, and different queries I've made in past only is either non updatable or partially updatable, which does no good. If anyone can show how one can retrieve only one record from the many side and keep it as updatable, that'd be great.
TIA.
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.
What am I doing wrong?
I have several comboxes where I'm using date/time input masks. When I go to enter data in the field the cursor is situated at the right end of the box. I have to backspace to the beginning of the field to enter the data. How do I position the cursor to the beginning (left side) of the field?
View 3 Replies View RelatedI 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.