Combine in onw query two tables with different number of records (1 Viewer)

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
Hi
I am back with my bizarre questions.
What I want now is to create a query which would include fields from two Tables. The problem is as follows.
Table 1 has fields: Date_abs as date; Cons_abs as string
It returns datasheet:
06/01/2019 LS, NC, AW am, EK
07/01/2019 TW, CK, AW am, EK
08/01/2019 LS, CM, MP pm, EK
09/10/2019 LS, NC, AW am, EK
10/01/2019 LS, NC, AW am, EK


Table 2 has fields: Date_abs as date; SPR_abs as string
It returns datasheet:
06/01/2019 EOH, IJ
08/01/2019 LJ, LJ, RK
10/01/2019 EOH pm

When I create a simple query including Date_abs; Cons_abs; SPR_abs with date_abs related, I get the result with 3 dates only:
06/01/2019 LS, NC, AW am, EK EOH, IJ
08/01/2019 LS, CM, MP pm, EK LJ, LJ, RK
10/01/2019 LS, NC, AW am, EK EOH pm

and I understand why. Because there are only three dates in Table 2.

But I want this datasheet as a result of my query:
06/01/2019 LS, NC, AW am, EK EOH, IJ
07/01/2019 TW, CK, AW am, EK
08/01/2019 LS, CM, MP pm, EK LJ, LJ, RK
09/10/2019 LS, NC, AW am, EK
10/01/2019 LS, NC, AW am, EK EOH pm

I wand all dates included and where there is no record for SPR_abs it should be left just empty of blank.

Is it possible at all?
 

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
I am sorry I tried to set spaces between fields but when I posted all spaces where contracted. I meant not one long string but three different fields for Date_abs, Cons_abs and SPR_abs.
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
If you used code tags #

Code:
6/01/2019 	LS, NC, AW am, 		EK EOH, IJ
07/01/2019 	TW, CK, AW am, 		EK
08/01/2019 	LS, CM, MP pm, 		EK LJ, LJ, RK
09/10/2019 	LS, NC, AW am, 		EK
10/01/2019 	LS, NC, AW am, 		EK EOH pm
I believe you could use Allen Browne' Concatenate function for both the abs fields and concatenate the returned values?

HTH
 

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
I believe you could use Allen Browne' Concatenate function for both the abs fields and concatenate the returned values?

HTH
I think its not exactly what I want. Concatenate function I understand when you want to stitch values from several records into one string. What I need is to show in my query records for all dates, not only for those which have related date in the second table. So If I have in table 1 five records and in table 2 just three records, then the query returns just three records with dates present in both tables, but two records are missing. I need all 5 records in a new query. How to do that?
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
Can dates exist in tableB that are not in tableA?
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
I am thinking that both abs fields should be in the same table?

I am still not sure if there is only one srs_abs field for a date or multiple and I cannot think of any easy way to do it.?

Best I can think of is

Create a union query of both tables for date field only and make distinct so you only get one date per record.
Use that to drive a second query that either DLookups the abs field or Concatenates them depending on your setup.?

HTH
 

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
I am thinking that both abs fields should be in the same table?
If it's impossible to do it via query, then I've got to try to create one table out of two tables. I don't remember why I decided to register them separately, Maybe because I used concatenate function for both both to have a string of people absent on the same day. But I hope I will sort it.
Many thanks.
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
I believe you can do it by query, just not one query. :D
 

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
I believe you can do it by query, just not one query. :D
Unfortunately I cant create a single table for both Consultants and SPRs absences. Because every absence is registered as a separate record. I need to keep consultants and SPRs in separate tables as they have different pattern of other activities.
 

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
It came to my mind though to create a different table for absentees. Set several fields like Cons_abs_1, Cons_abs_2, Cons_abs_3, Cons_abs_4 and SPR_abs_1, SPR_abs_2, SPR_abs_3 and so on up to theoretically possible number of absentees on one day. And then have just one record for each day. In this case I will overcome my problem, but I wonder whether such a decision would be a very very bad practice?
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
Unfortunately I cant create a single table for both Consultants and SPRs absences. Because every absence is registered as a separate record. I need to keep consultants and SPRs in separate tables as they have different pattern of other activities.
Well you have not given out much about your structure, but if you had a field to identify whether it is a consultant or spr, would that not solve the issue.?
I would have thought you would need the ID of that user in that record and from that you could identify whether a con or spr record?

So in effect you would just have an absence table?

Good luck anyway.
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
It came to my mind though to create a different table for absentees. Set several fields like Cons_abs_1, Cons_abs_2, Cons_abs_3, Cons_abs_4 and SPR_abs_1, SPR_abs_2, SPR_abs_3 and so on up to theoretically possible number of absentees on one day. And then have just one record for each day. In this case I will overcome my problem, but I wonder whether such a decision would be a very very bad practice?
Any time you have suffixes lke that, you have the wrong structure.
There should be one record per absence, so if there are 10 or n absences on a particular date, then there are as many records of the same number.

Now the concatenate works as intended.
 

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
I would have thought you would need the ID of that user in that record and from that you could identify whether a con or spr record.
You must be absolutely right. I need now to r4structure my database. I have two separate tables for details of consultants and SPRs. But now I need to have one with identifier. I will need to think how they will appear in combo boxes separately but that would be easy I believe.
Many thanks
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom