How to retrieve values that match between two tables?


I have two tables: "Delivery times" and "Receiving Times"

Both are joined by the PO number and the relationship Join Type is set to include only rows where both are equal.

Where the PO number matches, I want to include these rows. However there can be multiple instances of the PO number in each table.

Right now, For each row in "Delivery Times" the query is including every row in "Receiving Times," multiplying the result!

Example:
"Delivery Times"
1234567 | X | Y | Z
1234567 | X | Y | Z
1234567 | X | Y | Z
1234567 | X | Y | Z

"Receiving Times"
1234567 | A | B | C
1234567 | A | B | C

Query Result:
1234567 | A | B | C | X | Y | Z
1234567 | A | B | C | X | Y | Z
1234567 | A | B | C | X | Y | Z
1234567 | A | B | C | X | Y | Z
1234567 | A | B | C | X | Y | Z
1234567 | A | B | C | X | Y | Z
1234567 | A | B | C | X | Y | Z
1234567 | A | B | C | X | Y | Z

Expected Result:
1234567 | A | B | C | X | Y | Z
1234567 | A | B | C | X | Y | Z

How do I force Access to include the total number of combinations and not permutations?


Sponsored Links:



Hi friends,
I was working on a problem to get records that match in a table with the help of left function in my queries, now i want to get all records that dont match the criteria.How can I implement this in MS Access?
I was using in my queries the logic
(table1.[col1] = left(table2.[col1],4))




Hi,

I have a random number generated based on a query's record count. I would then like to retrieve a field value from that query based on the index that is generated.

The code below works and generates a random number based on the query's record count. But I do not know how to retrieve the EmployeeID field with the random index generated. I'm not sure if I need to open a recordset. I basically just want to go right to the record index that is generated and retrieve the EmployeeID, without having to loop through the records. Is this possible?

Code: Private Sub btnRandomEmployeeName_Click() Dim randomEmployeeNum As Integer randomEmployeeNum = Int((EmployeeCount - 1 + 1) * Rnd + 1) MsgBox randomEmployeeNum End Sub Private Function EmployeeCount() As Integer EmployeeCount = DCount("[EmpID]", "qryActiveEmployees") End Function




I've been trying to figure out how to extract extended file properties in Access. Apparently the FileSystem object only returns the standard set of file properties, such as the name of the file. Microsoft has a description of how to use VBA to retrieve extended file properties, such as the author of a file.

Additionally, stngman posted in UtterAccess an approach on how to retrieve extended file properties in Access.

The approach used by stngman, while it works, appears to require a lot of looping. I was hoping to avoid this looping since my program is already looping through the files. Alas, all that I have been able to get is the name of the extended property NOT the actual value. See the line of code in bold red.

Based on the Microsoft table, the file property with the value of "0" should return the actual name of the file, but all I get is the the name of the field which is "name". Any Thoughts?


	Code:
	Public Sub Create_File_List(InputPath As String)
    Dim CopyMP3RS As DAO.Recordset
    Dim Fso As Object
    Dim Fldr As Object
    Dim SubFldr As Object
    Dim F As Object
    Rem New Shell.Application Code Below -----------------------------------
    Dim objShell As Object
    Dim ojbFolder As Object   
    Set objShell = CreateObject("Shell.Application")
    Rem Rem New Code Above --------------------------------
    Set Fso = CreateObject("scripting.filesystemobject")
    Set Fldr = Fso.GetFolder(InputPath)
    Set CopyMP3RS = CurrentDb.OpenRecordset("SELECT * FROM tblMP3_FileList", dbOpenDynaset)
        For Each F In Fldr.Files
        Debug.Print "Name"; Fldr.Name, "type; "; Fldr.Type, "Path: "; Fldr.Path
        Set objFolder = objShell.NameSpace(Fldr.Path & "")       
        If Right(F.Name, 4) = ".mp3" Then
            CopyMP3RS.AddNew
                Debug.Print "File: "; F.Name, objFolder.GetDetailsOf(F.Name, 0)
                CopyMP3RS!FileLocation = F.Path
                CopyMP3RS!FileName = F.Name
                CopyMP3RS!FileSize = F.Size
            CopyMP3RS.Update
            End If
        Next F
    Rem ---------------------------------------
    For Each Fldr In Fldr.SubFolders
         Call Create_File_List(Fldr.Path)
    Next Fldr
End Sub





How to capture value in module
I have a value sitting in a standard code module I’d like my macro to access, what’s the best way to do that?




Dear All,
Anyone help me for how to pass value in subfrom update into main form automatic.
In this case i've two tables.
Example:
In the Input Form, at Language subform, you dropdown a list from "Ref Language"
you need a new language, you type "test", then "DB Language " table will include this new data: Ok
but "Ref Language" table will not include the new language "test" for future records.
Please help and thanks for your kind support




Hi to all..

Can anyone help me how to get value from query to form. I have form named productOrder, the field are productID,description, order,received,sold.

the rest of the field are key in by the user except sold which will get the value from query. How can i do that, what is the code..


Thanks...Hope to find answer.....




Hi there,

Here is my problem:

I have a form (based on a single table) which opens in a blank format (i.e. does not show records).

I now want to retreive a record by just entering its unique ID ("recordID" which happens to be an autonumber) into an unbound control (called "selecter")on the form ("frmOne"), so that the record's fields can be edited (deleted,added,changed,etc).

How to do this, without using a button ("on click" as if I would do if it was a filter) or combo/list box (which I think would not be feasibel - could not list all autonumbers in the combo-list...)?

So, then, how to retrieve a complete record by typing only its unigue ID (autonumber)?

Many thanks in advance for your time in answering this question




Hi,
I have a table with fields "Approval Date" and "Expiry Date" for each record. I want to retrieve records which fall in a period. (eg. active in 2010).

Then I make a parameter query [EnterYear].

[EnterYear]>=Year([Approval Date]) And [EnterYear]




How can I tell my VBA script to wait until previous process / action has finished before continuing with the next process / action?

Seems one process / action is not completed and VBA moves to next process / action which is creating lot of problems.

How to make sure that current process / action completed before moving to next process / action?




I have 2 tables in an access database, one is real and one is linked (docmd.transferdatabase aclink) the link & unlinking of this table works fine.

However I need to create a relationship of One-to many between the two tables, to do some reporting.

I have tried with createrelation but have been unsuccessful, can anyone help?

The details are as follows.

AccountNos Hazlehead
One Many
ServNo----------------Servno

Ian




Hi, I need some help with putting together a results that involves two different tables.

Say Both Tables have a common Field - Company Name

Table 1 has a less records with only one field - Company Name
Table 2 has a longer list with TWO fields - Company Name and City

The Company Names are similar in both tables
but I would like to display the results with Company name from BOTH Tables and Any cities associated with the name from table 1

Problem is that ie AT&T in table 1 won't link up to AT&T International in Table 2.

I know I can use the Like * wildcard but how do I link the two tables with the criteria of table 1 to find similar companies

Tables1

Company Name
AT&T

Table2

Company Name City
AT&T International Palo Alto
AT&T International London
AT&T Coporate London

Sorry if I sound very confusing, but my main questions is I suppose:


How do I write a query to find Similar * Company Names between two Tables

Thank you for any help you can provide.

Jaymus




[This message has been edited by Jaymus (edited 07-24-2001).]




Hi, Everyone,

I'm trying to adapt code for an .accdb form button in the main form class module that moves a piece of data from a field in one table to the same named field in another table. Not copies it, but actually moves it...as if it were being cut and pasted. The first table is full of "unused" movie codes, and I want the second to hold them as "used". I want to keep them separated with "a table between them" because I don't want any of these movie codes wasted (they cost money). I understand that programming between two tables instead of one is more complex, but from the user-end point of view, I think one table could get messy much more easier. Also, having the data move from table to table will simply be more aesthetic for my users who don't know much about Access. I understand that I could add a movie code "Status" field to the first table and write code that populates it as "used" once I have done doing with it what I'm going to, but I really, really want to move it between the two tables. I can't find public code that can execute this move, so I'm trying to adapt code that just copies between tables. So far, the block of code I have to "cut/paste" move it is as follows:

Code: Option Explicit Option Compare Database, Private Sub, etc. ..... ' This block moves (not copies) that same movie code from "Unused Movie Code Table" to "Used Movie Code Table". If gcfHandleErrors Then On Error GoTo PROC_ERR Public Const gcfHandleErrors As Boolean = False CurrentDb.Execute "INSERT INTO [Used Movie Code Table].MovieCode" & "SELECT TOP 1 [Unused Movie Code Table].MovieCode" & "FROM [Unused Movie Code Table]" & "ORDER BY MovieCode ASC", Debug.Print ("Move moviecode from Unused to Used table") PROC_EXIT: Exit Sub PROC_ERR: MsgBox ("Error moving movie code from Unused to Used table." & Err.Number & ") " & Err.Description, vbExclamation + vbOKOnly, _”Move Movie Code Between Tables” Resume PROC_EXIT Would this code move it or just copy it? Or not work at all? (I can't test it because it's a block in the middle of a bunch of other button automation code).

Alternately, I was advised to set-up the above code using sSQL statements first, followed by the CurrentDb.Execute statement...

Code: ' This block moves (not copies) that same movie code from "Unused Movie Code Table" to "Used Movie Code Table". sSQL = "INSERT INTO [Used Movie Code Table].Movie Code" sSQL = sSQL & " SELECT TOP 1 [Unused Movie Code Table].Movie Code" sSQL = sSQL & " FROM [Unused Movie Code Table]" sSQL = sSQL & " ORDER BY Movie Code ASC" Debug.Print ("Move moviecode from Unused to Used table") CurrentDb.Execute sSQL PROC_EXIT: Exit Sub PROC_ERR: MsgBox ("Error moving movie code from Unused to Used table." & Err.Number & ") " & Err.Description, vbExclamation + vbOKOnly, _”Move Movie Code Between Tables” Resume PROC_EXIT Is this just a layout thing to read the code easier? Will the code run better using this sSQL structure with the Execute command at the end? I thought that method was outmoded?

Thank you so much for taking the time to look at this code problem. You guys have been consistantly educating me in the past month, and saving my neck on a surprise coding project that is a true trial by fire. You'll never know how your unselfishness with your knowledge is appreciated. Any thoughts on this are welcomed.

Frank




Hi all.

I am new to access.

Does anyone know how to pass value from TextBox to ComboBox in VBA?

Controls in my form:
ItemCode textbox - user to key in the itemcode
SpeID1 textbox - LEFT(itemcode,2)
SpeID combobox - pass value from SpeID1 textbox to SpeID combobox
Desc textbox - Dlookup Desc from Species table using SpeID

The Desc will come out if I run the following code by choosing the value from the dropdown combobox without using the SpeID1 textbox
Code:

Private Sub SpeID_AfterUpdate()Desc = DLookup("Desc", "Species", "SpeID='" & SpeID & "'")End Sub
but what I want here is the user need not to choose the value from the SpeID Combobox and the SpeID and Desc will come out automatically after the user enter itemcode.

I try to use SpeID1_AfterUpdate() but no value appears at the Desc.

Anyone here kindly share your advice with me.

Thanks.




How do I create a report using the fields from two tables (a multi table query can be used or a second table can be added to the design)?




Hi,
Can anybody help on this
how to find list of tables in a database and how to identify a common column between those tables

Thanks in advance
Padma




Thanks in advance

Searched birthday in threads and couldn't figure this out.

I have a select query and I am trying to retrieve a birthday that is between two dates.

This is how I set up my expressions:

chkBirthDay: Format([BirthDay],"m/d")

Criteria for chkBirthDay Between Format([LaneDate]-3,"m/d") And Format([LaneDate]+3,"m/d"

So if LaneDate = 9/7/2005 I should be able to retrieve all of the Birthdays that fall within 9/4 and 9/10. Correct?

I do retrieve records but they are dates such as 9/17/1921 and 9/18/1948 but of course they do not satisfy the criteria. There is a date in the table that does, 9/7/1981, but that record does not show up?

Any suggestions?




Thanks in advance for everyone's help

I am trying to match 2 columns in one worksheet with 2 (or potentially more) columns in another worksheet. I have attached my excel which hopefully gives a good description of what I have been trying to do for the past week. I think I am on the right track but I don't know how to properly reference between the two worksheets.

Thanks!

If you have any question I'll do my best to answer them

Hova




hi

I need help here. I've been trying to fill in the textbox with some data in the report but it just won't let me assign a value to it.

What's happening is i have two tables, tableA and tableB. The report is based on tableB however there are some fields such as contact, phone, reference that are empty in tableB that can be filled in by looking up on tableA. I know that i can link the two tables together by ID or something but i'd rather do it this way to avoid duplicates

My question is as soon as the report is open, i need it to check if the contact field is empty or null, then look up tableA to retrieve the value of contact and then assign the value to the textbox created for contact in the report.

Hope that i don't confuse you.

Your help is greatly appreciated

Thank you in advance




hi,
I wish to retrieve a particular value from the table and use it for comparison and calculation purposes .
In the VBA code i tried using Dlookup function , but it returns a variant and i can't compare with an integer. Is there any possible way where i can extract tht value as an integer so that i can do comparison and calculation.

Thanks in advance