One Side Of 'outter Join' Issue.

Dec 26, 2006

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.

View Replies


ADVERTISEMENT

Tables :: Cannot Enter Value Into Blank Field On (one) Side Of Outer Join

Apr 28, 2014

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?

View 10 Replies View Related

Forms :: Can't Enter Data Into Blank Field On One Side Of Outer Join

May 21, 2014

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.

View 1 Replies View Related

Reports :: Report To Show Records Side By Side?

Aug 12, 2015

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.

View 3 Replies View Related

Server Side Vs Client Side Queries

Nov 22, 2005

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 Related

Form With Three Sections To Show Side By Side

Jan 26, 2012

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 Related

Delete Only FK On The Many Side And The Record On The One Side

Jun 25, 2007

Hi,

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

View 4 Replies View Related

Reports :: Cut Off Left-hand Side Of Text In Textbox And Show Only Right-hand Side

Mar 26, 2013

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?

View 2 Replies View Related

'ambiguous Outter Joins' Message And Decode

Oct 24, 2007

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:

http://www.halstedhome.com/images/temp/access_screenshot.jpg

I hope that image post works.

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.

View 5 Replies View Related

WHERE Clause On LEFT JOIN : Why Do I Get Join Expression Not Supported Message?

Nov 10, 2006

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";

but I get Join Expression not supported

What am I doing wrong?

Thanks
Andrew

View 7 Replies View Related

Create A JOIN Of Different Tables Called Join A Variable And List

Nov 16, 2013

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?

View 6 Replies View Related

No Record On Many Side

Nov 5, 2005

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

View 4 Replies View Related

One-to-many - For Each One, Count Corresponding Records On Many Side

Mar 16, 2006

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.

View 4 Replies View Related

Use Of Access For Server Side Programming

Jan 20, 2008

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:

View 2 Replies View Related

Concatenating The Many Side Results Into One Result

Dec 26, 2006

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

View 7 Replies View Related

Left Side Tree Menu

Sep 24, 2006

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

View 1 Replies View Related

Server-side Access DB Performance

May 8, 2007

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!

View 2 Replies View Related

Query - Show All Records On The Many Side Of A 1-many Relationship

Aug 29, 2007

I 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

View 8 Replies View Related

Date Range 7 Days Either Side Of Today

Sep 13, 2007

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

View 11 Replies View Related

Relational Problems, Add New Record To Many Side Of One-to-many Relationship

Mar 22, 2005

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

View 3 Replies View Related

I Want To Put Text Down The Whole Left-hand Side Of A Form!

Jul 10, 2006

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...

View 3 Replies View Related

Modules & VBA :: Delete All Charters On The Right Side Of Last Comma

Jan 25, 2014

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)))

View 6 Replies View Related

General :: Backup Database At User Side

Oct 4, 2012

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 Related

General :: Find Special Character From Right Side

Sep 23, 2013

I 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 Related

Tables :: Cannot Enter Data On One Side Of Many To Many Relation

Jan 30, 2014

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.

View 14 Replies View Related

Textbox Values Not Valid Because Of Misterious Side Effect

Feb 26, 2007

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 Related







Copyrights 2005-15 www.BigResource.com, All rights reserved