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?


Post your answer or comment

comments powered by Disqus
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

Hi all,

I'm currently trying to fix a database that hasn't been working properly and have come unstuck trying to run a report between 2 dates.

Basically, each report shows a date (DayDate) and belongs to a certain Contract (Contract). I have developed a form with a combo box (cboContract) that offers all possible contract numbers and two boxes where the user inputs StartDate and EndDate.

What I'm trying to do is work out what code I need to ensure that when the user presses the button Run Report (Command19) the report (rptDayworkCheck) will run between the StartDate and End Date. The code I have so far (from miscellaneous sources is as follows)

Code:
Private Sub Command19_Click()
On Error GoTo Err_Command19_Click
Dim stDocName, Contract As String
Dim StartDate As Date
Dim EndDate As Date
Dim stLinkCriteria As String

StartDate = Forms!frmContracts1!StartDate
EndDate = Forms!frmContracts1!EndDate
Contract = Forms!frmContracts1!cboContract

stDocName = "rptDayworkCheck"
stLinkCriteria = "[DayDate] Between # " & Format(StartDate, "dd/mm/yyyy") & "# And #" & Format(EndDate, "dd/mm/yyyy") & "#"
DoCmd.OpenReport stDocName, acPreview, stLinkCriteria


Exit_Command19_Click:
Exit Sub
Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click

End Sub

Any help would be greatly (and desperately) appreciated!

Hi,

I have a file that has a field that contains both company name and numeric data. I have tried parsing the data in excel but the company name has multiple words with each word separated by a space. I cannot figure out how to replace the spaces between the company name words with an underscore. I have been trying to add an underscore between two alpha characters. I think that would work and then I could parse it properly from there. Is there a VBA solution to this and also to parsing the data?

Can anyone help.

Thanks

Ian

I'm trying to make a simple database in access for work, i'm not brilliant with access, i know enough to make simple tables, forms and macros.

I'm trying to calculate the difference between two dates that i will be entering into two fields on a table.

Its a hire car company i need the difference between the date a car is hired out and the date it comes back in. I then want to multiply that number by the hire rate, say its £55 a day and the car is hired for 10 days.

I then need the result to be displayed in a field, possibly by using a button that says 'Calculate' or 'Hire cost'

eg. 01/04/2009 to 10/04/2009 inclusive multiplied by a hire rate of 55.

I have a table with the cars and thier rates and a table for this calculation (along with some other tables for clients etc.)

Is this possible? maybe using datediff? i've seen a few pages via google that say to use this but i can't work out how to do it. if anyone can help i'd really appreciate it.

thanks
Dan

I am a newbie to access and i am working on a project that has two tables
the primary table has for example the following fields:

firstnameID(primary key) data type auto number
Firstname data type text

the second table has for example :
LastnameID(primary key) data type auto number
Lastname data type text
FirstnameID (foreign key)data type number

I created a relationship between the FirstnameID in the primary table and the FirstnameID in the second table ..
My problem is this whenever i try to view the relationship when i open the primary table it don't show the relationship ,
The Field FirstnameID in the secondary field is always Empty the only time the relationship is created is when i manually insert the autonumber that is generated in the FirstnameID field , but i thought that since a link was created then that field would have automatically be inseted with data , am i assuming wrong or am i doing somethingn wrong? how do i get the autonumber to be inserted in the field automatically to create the relationship?
Please Help

Hi, I've two tables:

Dispatch
-----------------------------------------------------------------------
DispatchID | CustomerID | DispatchDate | Service | ConsignmentID
-----------------------------------------------------------------------
1 1 2008-07-04 Zippy AZ432-566

DispatchItem
-----------------------------------------------------------------------
DispatchItemID | DispatchID | OrderItemID | NbrDispatched
-----------------------------------------------------------------------
1 1 1 1

In the above tables: In Dispatch, DispatchID is autonumber and is a primary key, CustomerID is a foreign key and can be repeated, DispatchDate is the date of the dispatch, Service is the name of service used to dispatch items and ConsignmentID is just some ID and can be null.

Similarly, in DispatchItem, DispatchItemID is autonumber and a primary key, DispatchID is a foreign key, OrderItemID is a foreign key and NbrDispatched is the number of item(s) dispatched.

Now, when new items are dispatched I inserted CustomerID, DispatchDate and Service and left the ConsignmentID as it can be null and DispatchID as it increments automatically. Now, the problem I'm facing is, I want to insert details in DispatchItem as well and, no where in the world, I could figure out how I can insert the same DispatchID that got inserted while inserting other details in Dispatch table in DispatchID column in DispatchItem table. Also, I'm wondering how I can include query in insert statement for OrderItemID coz it has to be the one where OrderNbr = ? AND ItemID = ? From other table. I'm using VBA for the insert and update from a form. Thanks.


Not finding an answer? Try a Google search.