Find value in another table and return ID

[Hoenen]

Hi there, I'm currently busy with something for my thesis as a student and I need to use Access for this. I'm not too new at access, I know how to do the very basics, let's say on the level of [if field contains *"text*", return x].

However I am struggling right now on something that shouldn't be too hard... I could do it immediately in Excel if there werent millions of rows..

I'll explain:

I have 2 tables.

Table 1 regards a list of patent publication numbers (eg. WO2012024604A3) and additional data (publication date, title, etc), only the publication number matters for me now.

Table 1

[INDENT]publication number[/INDENT]
[INDENT]WO2011085209A2
WO2011100754A1
WO2011112983A3
EP2342192A4
EP2342192A2
EP2205725A2
EP2205725A4
WO2012006540A3
WO2010008486A3
WO2012083136A1
[/INDENT]

Table 2 contains another list of patents that might cite/refer to Table 1's patents and additional data such as publication date.

Table 2		

[INDENT]Publication Number Citing Patents	Publication Date	        Cited Refs - Patent

AU2001287375B2	                        1998-12-01	                US5178882A | US4225581A | WO1998001161A2
AU2001288365B2	                        1990-02-24	            	US5967154A | WO1996039117A2 | US3699979A | US3943949A | US3838702A
AU2001288437B2	                        1999-03-09	            	US6087157A
AU2001288561B2	                        1990-03-01	            	US5775675A | US398078A | US5078366A | US446852A
AU2001288840B2	                        1999-04-26	            	US5507813A | US6090998A | US5899939A | AU200143150A | AU2001043150A1 | US6123731A | WO2000029037A1 | US6761739B2
AU2001289110B2	                        1999-07-06	            	US2556783A | WO1997035522A1 | US4611594A | WO1999048429A1 | WO1999056801A2 | US4347846A | US4046150A | EP818180A2 | US5397320A
AU2001289196B2	                        1999-07-21	            	US5311095A | US6049158A | US6328697B1
AU2001290088B2	                        1990-03-05	            	US5346458A | WO1995028127A1 | US4979936A
[/INDENT]

Now what I'd like to do is to create a third table which has for each of [Table 1].[Publication number]:

Column 2) A count of how many times the [Table 1].[Publication number] is found in [Table 2].[Cited Refs - Patent] ...

Column 3) In case a patent is cited more than once, return the [table 2].[publication Number Citing Patents] value of the earliest citing patent (so with the lowest Publication Date value).

---

For Column 2 I had expected it to be an easy count(iif( [Table 2].[Cited Refs - Patent] = "*"&[Table 1].[publication number]&"*")) command but apparently it's harder than that..

Can anybody guide me towards the right ways to create this third table?

 

[plog]

Not to get all Confuscious on you, but the 3rd table you seek isn't the 3rd table you need. What you want should be done with a query. And if your data were structured properly would be simple to achieve.

What you need is a 3rd table that properly holds your Cited Ref data. That data shouldn't be in Table 2, it should be in its own table. Let's call it Citations and it looks like this:

Citations
CitingPatent, ReferencedPatent
AU2001287375B2, US5178882A
AU2001287375B2, US4225581A
AU2001287375B2, WO1998001161A2
AU2001288365B2, US5967154A
AU2001288365B2, WO1996039117A2
AU2001288365B2, US3699979A
AU2001288365B2, US3943949A
AU2001288365B2, US3838702A
AU2001288437B2, US6087157A

From there you will be able to create a query to find out when each ReferencedPatent was first used. I know you have a huge amount of records, so to get from where you are to where you need to go, you will need to use some VBA. Your problem isn't unique, it has been addressed a bunch of times on this forum. Search it for terms like "expanding data", "normalizing from comma seperated field", "split field values" and such.

I did a quick search myself:

http://www.access-programmers.co.uk/forums/showthread.php?t=234113&highlight=split+field+values

http://www.access-programmers.co.uk/forums/showthread.php?t=258931&highlight=split+field+values

 

[Hoenen]

Thank you, I will look into it.