General :: Raw Data And Random Join

Aug 22, 2012

I have almost all of my info with the exception of few hundred pieces of data entered into my database. After looking it over and looking for ways to improve it, would it be easier to import all the tables into a brand new database and starting fresh with everything? I have made a lot of changes since the inception of the database so there are forms and queries and reports that no longer work because of the changes. Or should I go back and change them?

Also, if I am looking to join to tables can they be joined by their foreign keys? They are listed below:

tblEmpInfo
EmpInfoID <PK>
EmpIDFK <FK>
JobNumberFK (joined to tblJobs)
EmpType
CraftCode (would get rid of this)

tblEmpRating
EmpRatingID <PK>
EmpID <FK>
EvalDate
SuptID
StatusChange
CraftCode

I want to join the tables based on the EmpID can this be done or do I have to add another field to EmpRating ID?

View Replies


ADVERTISEMENT

General :: Database Becoming Read Only At Random?

Jun 24, 2014

I'm sure it's not random, but it doesn't seem to follow any sort of rules. My database has become "Read Only" 3 times now causing all workers using the database to stop work until IT can fix the issue. All IT does is kick everyone out of the folder that my db is in and suddenly it is no longer read only.

View 2 Replies View Related

General :: Random Number And Comparison

Oct 22, 2013

Anyway, here is what I am looking to do:

1.) Generate a random number between 1 and 100 and assign the result an identifier, like a person's name.
2.) Generate a second random number between 1 and 100 and assign the result a different identifier, like another person's name.
3.) Compare the two randomly generated numbers.
4.) Have the identifier of the LARGER random number displayed on the screen.

I would like to have this linked into a button click.I am hoping there is an easy way to accomplish this task.

View 3 Replies View Related

General :: Random Selection From Large Dataset

Jun 25, 2013

I pulled a report from this website my company uses and it has around 7,000 orders and a corresponding employee to each record. Since each employee has around 20-50 orders, I was wondering if there was a VBA code or different Access tool to randomly select only 2 orders from each employee, thus reducing the data set from 7,000 to a more manageable number?

View 5 Replies View Related

General :: Random Image Selection On A Form

Jul 9, 2015

random image selection from a form on a buton click event.To be more explicit: i have 7 images on form, when i push one buton i want to see how each image take focus (when take focus .BorderColor =vbRed after lose focus .BorderColor =vbWhite) and after lets say 20s focus stop random on one image and keep that image with .BorderColor =vbRed.

View 14 Replies View Related

General :: Generating Random Project Numbers With Date

Jun 7, 2012

How to generate random project numbers that reset automatically every month? I want to generate project numbers that look like this:

1234/06/12

where the 06/12 stands for the month and the year while the 1234 is a randomly generated number.

I want the random number to be four digits long.

I already have a number of projects with similarly generated projects from an existing database. Can I import these projects without changing their project number?

View 3 Replies View Related

Random Selection Of Data

Dec 18, 2006

Hi all

I would like to find out if there is a program where you can randomly select data out of a table?

I sit with a table with 17000 SKU's(codes and data) and need to randomly select items out of it for Stock Count's:confused:

Thanx:rolleyes:

View 6 Replies View Related

Random Data Retrieval

Jan 30, 2006

Hi,

Can someone please help. I am required to bring back a ranadom 10% of data where the [Total_Cost] field is less than £500.

Can this be done in one query? I have to use Access97 becuase this is the lowest Office Version we support.

:confused: .

View 1 Replies View Related

Tables :: Random Missing Data After Importing From Excel To Access

Mar 8, 2014

I've got an Excel sheet with +700k rows and 20 columns that I wanted to import to Access. All fields are text except the field that I want to use as a primary key, but I planned to import that as a text as well.

When I used the import wizard, I set all fields to import as text except for three that I set to memo. The wizard didn't say there was any error after importing the data, but when I checked the table, I noticed there were *a lot* of records where many fields where blank. Some fields where completely unaffected by this problem throughout the entire table, but in the rest of them, there is data missing in many records, and when there is data missing, it is not always the same fields that are missing. I have been unable to find any pattern that explains why sometimes the records were imported correctly, and why sometimes they were not.

View 2 Replies View Related

General :: Join Key Of Table Not In Recordset

Oct 12, 2012

I have a table orders details with a sub form displaying the order, I have a combo box to display the product from products table to place in the sub form products box . I keep getting the error message join key of table details not in record set I have checked my relationships and for keys and there all there .

View 1 Replies View Related

General :: Inner Join - Copy All Columns From Table

Jun 24, 2013

I have some code. I want to copy all columns from

Code:
tabela_zrodlowa = "tblGoraZlecenia"

but only one column which is called "Id_rodzajpracy" from

Code:
tabela_zdrodlooffset = "tblOffset"

How could I modify red instruction to do inner join?

Code:
Dim rec_GoraZlecenia As DAO.Recordset
Dim rec_GoraZlecenia2 As DAO.Recordset
Dim rec_GoraZlecenia3 As DAO.Recordset
Dim tabela_docelowa As String
Dim tabela_zrodlowa As String
Dim tabela_zdrodlooffset As String
Dim s As String

[code]....

View 5 Replies View Related

General :: Join Key Of Personnel Table Not In Recordset

Jan 26, 2015

I am fairly new to access, and have the following problem with the attached new DB. Thought I have designed the start of a good database with a "Junction Table". Have 1 problem, cannot enter a new name on the from, gives the error below:

"Join key of personnel table not in recordset" ...

View 2 Replies View Related

General :: Cross Join To Create Ledger Type Query From 4 Tables

Apr 2, 2013

I have 4 queries in which data needs to be connected from the date and shown as a single date showing each sections entry in a row and a cumulative total is maintained as the balance .

See the attached image ...

View 7 Replies View Related

Input Data With Form On Join

Dec 3, 2005

Hi,

I try to input data in table 1 with a form that is based on a join from table 1 and table 2.

I do a right join, so if there are no records for the join condition in table 1, null values are created. My idea is to change these null values, so 'real' values exist in table 1.

Can anybody help me, or show an alternative way to do this?

Thnx in advance.

Freak

View 1 Replies View Related

Queries :: Join Data Between Two Tables?

Sep 3, 2014

I have two tables

first table : customer name,QUANTITY ,delivery date,product type

second table : customer name,QUANTITY ,delivery date,product type

i want to make query to sum the QUANTITY from the two tables and group by customer name and delivery date

View 14 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

Change Data Type/ Join Mismatch

Apr 30, 2006

Looks like mismatched join types has caused others plenty of headaches in the past!!
I am trying to create a query that connects 2 separate areas of our factory.
The first table (Table 1) has a field called prod_code which is a text field and this code (5 digit number) describes a manufactured product.
The next table (Table 2) also has a field called prod_code which is also a text field, is a different 5 digit number, and describes the same product but after packaging.
There is a 3rd table which I want to use to link both of these tables, but in table 3:
table 1. prod_code = table 3.item_code, and is a long integer. and table 2.prod_code = table3.item_code_prnt, also a long integer.
I need to be able to use table 3 to correlate data from tables 1 and 2.
I suspect I need to use Clng or similar but am unsure how to apply it.
Have been trying to construct an expression but continually get error (bracketing error, join mismatch etc, etc)
Any clues?

View 3 Replies View Related

Queries :: Join Data Of Two Tables Using Query

May 3, 2015

I have two tables with name of accone and the second with the name of acctwo. These two tables are same according to number of columns and also same according to data types and also same according to the column names just the data are difference and also one column (attribute) with the name of ID is same in both tables. Their is a primary key relationship (one-to-one) between these two IDs. I need a query that can combine the data of both of them and can be updated using query. I mean that data of first table and second table must become under one same column not two columns one for first table and second one for second tabel.

View 4 Replies View Related

Queries :: Inner Join - How To Return Data From Table1 Only

May 7, 2013

How to return data from table1 only if table key matches table2 key.

I tried this but doesnt return me the data i want.

SELECT A.*
FROM tbA AS A INNER JOIN tbB AS B ON A.[ keyfld] = B.[keyfld];

View 9 Replies View Related

Modules & VBA :: ADO - How To Delete Data From Only One Table From A Join

Dec 17, 2013

How to Delete data from only one table from a Join? OR How to set the ADO recordset unique table property?

On Access 2010 module I have a class that manipulate data (save, read, edit and delete) from this statement:

Code:
Private Const strNomeTblFonte as string = _
"SELECT ER.*, ET.intTipoExame, ET.txtNomeExame, FROM tblExamesTipos
ET INNER JOIN tblClientesExamesRequisitados ER ON ET.idExamesTipos = ER.intQualExame;"
Private Sub Class_Initialize()
Set mCol = New Collection

[Code] ....

Problem: I need to delete data from only one table (tblClientesExamesRequisitados) of a inner join, but only delete from the "wrong" (tblExamesTipos) table.

After going to msdn on title: Unique Table, Unique Schema, Unique Catalog Properties-Dynamic (ADO) I attempted to address the problem with this line in the Class_Initialize():

Code:
Recordset.Properties("Unique Table").Value = "tblClientesExamesRequisitados"

But only generates this: Run Time Error 3265 - Item not found in this collection...

I know, if I open another Rst and use a Distinctrow, or open only one table, as in "DELETE * FROM tblClientesExamesRequisitados WHERE intQualExame = " & miQualExame & ";" it will resolve, but, why "Unique Table" isn't functioning?

View 3 Replies View Related

Correct Join - Not Bring Required Data Back?

Jan 21, 2008

Hi ,

recently posted a thread about a union query. I have got over that hurdle now but face a new problem which is:

When I have created a right join to bring back data from a table, if there is no data it will leave the fields blank. Is there any way I could bring back the row data of the same table.??

Hope some one could suggest something.

Thanks again.

View 9 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

Modules & VBA :: Join Multiple Values Into String For Summarizing Data On Reports And Exports

Mar 14, 2014

I have to join multiple values into a string for summarizing data on reports and exports. This process in vba is taking up to 10 minutes to process and will get worse as the size of these reports grow.

My method so far is to query the individual items into a recordset, loop through the values, adding them to the string then return the string in the query.

Here is an example:

Public Function SO_Description(intSO As Integer) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLSelect As String
SO_Description = "Profiles: "
Set db = CurrentDb

[Code] ....

View 1 Replies View Related

Inner Join And Left Join

Jan 3, 2007

I am trying to do an inner join with a left join. The only problem is, I want to inner join the table that is being joined. This is how I thought it would work below, but it doesn't work.

SELECT * FROM ((Submissions LEFT JOIN Candidates ON Submissions.`Candidate Code` = Candidates.`Candidate Code`) INNER JOIN `Type Candidate Status` ON Candidate.Status = `Type Candidate Status`.`Status ID`)
WHERE Submissions.Status <> 7 ORDER BY `School Interest` DESC;

I want to get the Candidate.Status to inner join with the `Type Candidate Status`.`Status ID`.

If you can help, thanks in advance.
Dave

View 1 Replies View Related

General :: Updating Table Data With Data From Preceding Entry?

Jan 5, 2013

My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:

ID WorkerNumberDateworkedTimeStartTimeEnded
121/2/201310:00:00 AM3:00:00 PM
221/3/20132:00:00 AM11:00:00 AM
321/4/201312:15:00 AM11:30:00 AM
421/5/201310:25:00 PM11:00:00 AM
531/2/201311:00:00 AM3:30:00 PM
631/3/201312:00:00 PM10:00:00 PM
731/10/20137:00:00 AM4:00:00 PM

I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.

Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).

View 4 Replies View Related







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