Creating a relationship between two uncommon tables. (1 Viewer)

Mr. Southern · Dec 17, 2019

I may be overthinking this but I have two tables without a common link. Eventually, I am trying to create a report that compares two different years of data. The two tables were created from append queries. Is there any way I can create an additional column that shows the row number? I would like to find the difference between workdays. For example (see picture), some days will match up but others will not (day 4).

theDBguy · Dec 17, 2019

I don't see why not... At least, it's worth a try, right?

Micron · Dec 17, 2019

If there's no linking field, why would anyone presume that ID 1 in table 1 has anything to do with ID 1 in table 2? If those are autonumber fields you're thinking of joining together the idea violates a basic tenet, which is that autonumber PK's should never be thought of as meaningful data. If it produces realistic records, it's a major fluke, and a lucky one at that.

Mr. Southern · Dec 17, 2019

I do not have Autonumbers in this table. I am trying to figure out a way that I can link the two tables. I have seen a few things on RowNumber but haven't been successful with it.

June7 · Dec 17, 2019

It may be the only practical way to accomplish what you want. How do you want to populate these two row number fields? Manual data entry or VBA procedure or calculate in query?

BTW, they could actually be 1 table. Do you have a table for each year? How many years?

Date and Day are reserved words and advise not to use reserved words as names for anything.

Day field is not needed as this can be calculated from Date.

Should Accounts field name actually be Amount?

Mr. Southern · Dec 17, 2019

June7 said:
It may be the only practical way to accomplish what you want. How do you want to populate these two row number fields? Manual data entry or VBA procedure or calculate in query?

I do not want to manually add. It will be roughly right around 200 rows of data. What do you recommend?

BTW, they could actually be 1 table. Do you have a table for each year? How many years?

Yes, I have two tables. I will only be looking at the previous year and the current year.

Date and Day are reserved words and advise not to use reserved words as names for anything.

Day field is not needed as this can be calculated from Date.

I will be changing these field names eventually.

June7 · Dec 17, 2019

Consider this query approach:

SELECT Q1.*, Q2.*, Q1.Amount-Q2.Amount AS Diff
FROM (SELECT EntryDate, Amount, DCount("*","Table1","EntryDate<=#" & [EntryDate] & "#") AS T1Seq FROM Table1) AS Q1
INNER JOIN (SELECT EntryDate, Amount, DCount("*","Table2","EntryDate<=#" & [EntryDate] & "#") AS T2Seq FROM Table2) AS Q2
ON Q1.T1Seq=Q2.T2Seq;

Mr. Southern · Dec 17, 2019

June7 said:
Consider this query approach:

SELECT Q1.*, Q2.*, Q1.Amount-Q2.Amount AS Diff
FROM (SELECT EntryDate, Amount, DCount("*","Table1","EntryDate<=#" & [EntryDate] & "#") AS T1Seq FROM Table1) AS Q1
INNER JOIN (SELECT EntryDate, Amount, DCount("*","Table2","EntryDate<=#" & [EntryDate] & "#") AS T2Seq FROM Table2) AS Q2
ON Q1.T1Seq=Q2.T2Seq;

From what I can tell, it seems to be working BUT access freezes up after I run the query lol.

June7 · Dec 17, 2019

It worked for me with sample data provided. If you want to provide db for analysis, follow instructions at bottom of my post.

Mr. Southern · Dec 17, 2019

June7 said:
It worked for me with sample data provided. If you want to provide db for analysis, follow instructions at bottom of my post.

It works if I change it to a make query..... Link doesn't work

June7 · Dec 17, 2019

You mean a MAKE TABLE query? Not sure what you mean by 'Link doesn't work'.

Mr. Southern · Dec 18, 2019

Yes, a make table query. The cpearson debugging link doesn't work.

June7 · Dec 18, 2019

Too bad. I will remove the link, however, it has nothing to do with suggestion to provide db for analysis.

Mr. Southern · Dec 27, 2019

Sorry for responding so late. A few things came up. I was able to get it to work based on your suggestion. I appreciate your help.

theDBguy · Dec 27, 2019

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

jdraw · Dec 27, 2019

The cpearson debugging link doesn't work.

Yes that has been identified just recently --too bad. I have a link in my signature to a youtube by Steve Bishop that may be helpful.
Good luck

Creating a relationship between two uncommon tables. (1 Viewer)

Mr. Southern

Registered User.

Attachments

theDBguy

I’m here to help

Micron

AWF VIP

Mr. Southern

Registered User.

June7

AWF VIP

Mr. Southern

Registered User.

June7

AWF VIP

Mr. Southern

Registered User.

June7

AWF VIP

Mr. Southern

Registered User.

June7

AWF VIP

Mr. Southern

Registered User.

June7

AWF VIP

Mr. Southern

Registered User.

theDBguy

I’m here to help

jdraw

Super Moderator

Similar threads

Users who are viewing this thread