Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-23-2019, 12:55 AM   #1
Chrismeli
Newly Registered User
 
Join Date: Jun 2019
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Chrismeli is on a distinguished road
Appending Query Multiplies Data

Hello!
I am trying to build a database which will consist of 3 tables and many more Queries which will randomly allocate police officers to the required , for the day, duties.
I am in a police department in Athens Greece which has more than 250 officers and currently there are 8 people trying to allocate everyone daily according to a book they keep everyone on.
Now what i did is i created 3 tables as you can see and the problem I face is that the appending query i want to use, so each and every police offer is allocated randomly to each post, gets multiplied by the data that the tableb has and does not combine them to give me the results i want

More specifically we have :

```
TABLEA
ID | POLICE RANK | FULL NAME |
____________________________________
288066 | Const. | Chris Meli |
273111 | Serg. | John Do |
231444 | Const. | Bill Park |
298432 | Const. | Joe Park |
_____________________________________
```
which contains the info of the police officers and is connected to the ID field in TableC so even from the connection on TableA you can examine the duties every officer has been assigned to the previous days.

```
TABLEB

DUTY | Number of Police needed |
| for each service |
____________________________________
Patrol | 1 |
Guards| 1 |
Courts | 2 |
____________________________________
```

I put the number 1 and 2 just for the sake of simplicity. Normally TableA will contain *more than 250 people* and on *TableB will be many Duties and the number of police needed will vary depending on the date and many other factors.

```
TABLEC
ID | DUTY | DATE |
____________________________________
.......|.................| ................|
.......|.................| ................|
.........|.................| ................|
.........|.................| ................|
_____________________________________
```
TableC will be populated From TableA (ID),TableB (Duty) and an input for the date i will be scheduling with the following appending query

```
INSERT INTO TABLEC ( DUTY, DATE, ID )
SELECT TABLEB.DUTY, [INPUT DATE], TABLEA.ID
FROM TABLEA, TABLEB INNER JOIN n ON n.n <= TABLEB.[Number of Police needed for each service];
```
(n is a numbers table and n.n is a column that has like 10000 numbers so don't pay attention to that)
Now the appending query returns me the results i need but what happens is , it kind of multiplies the position needed with the officer's ID . So instead of having this:

```
TABLEC
ID | DUTY | DATE |
____________________________________
288066 | Patrol | 23/06/2019 |
273111 | Guards | 23/06/2019 |
231444 | Courts | 23/06/2019 |
298432 | Courts | 23/06/2019 |
_____________________________________
```
I have this:
```
TABLEC
ID | DUTY | DATE |
____________________________________
288066 | Patrol | 23/06/2019 |
288066 | Guards | 23/06/2019 |
288066 | Courts | 23/06/2019 |
288066 | Courts | 23/06/2019 |
273111 | Patrol | 23/06/2019 |
273111 | Guards | 23/06/2019 |
273111 | Courts | 23/06/2019 |
273111 | Courts | 23/06/2019 |
231444 | Patrol | 23/06/2019 |
231444 | Guards | 23/06/2019 |
231444 | Courts | 23/06/2019 |
231444 | Courts | 23/06/2019 |
298432 | Patrol | 23/06/2019 |
298432 | Guards | 23/06/2019 |
298432 | Courts | 23/06/2019 |
298432 | Courts | 23/06/2019 |
_____________________________________
```
Is there a way to connect TableA.ID and TableB.DUTY without being multiplied automatically?at
I appreciate any input. I asked the same question at stackoverflow and someone prompted me to search for inner and left joins. I have looked everywhere but still cant figure it out!
Now the question is how to specify it in a join type? :/

Chrismeli is offline   Reply With Quote
Old 06-23-2019, 05:51 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,035
Thanks: 36
Thanked 721 Times in 704 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Appending Query Multiplies Data

Hi Chris. As I was saying in the other forum, you’re getting this result because of a cartesian query. Can you post a copy of your db with test data?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-23-2019, 07:01 AM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,938
Thanks: 74
Thanked 1,969 Times in 1,917 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Appending Query Multiplies Data

Forgetting tables and database terms for the moment, what is it that needs to be accomplished -simple description, plain English?

How many "duties" are there really? Seems Patrol, Courts, Guards only.
It also seems you are scheduling for current day only. Is that correct?

If we had a 30,000 ft overview of the requirement, it might be helpful.
Good luck.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 06-23-2019, 07:26 AM   #4
Chrismeli
Newly Registered User
 
Join Date: Jun 2019
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Chrismeli is on a distinguished road
Re: Appending Query Multiplies Data

Right Away sorry for the Delay

ΠΡΟΣΩΠΙΚΟ is TABLEA (consists of the details of "all" the police officers)

ΟΜΑΔΕΣ is TABLEB ( which consist of all the duties probably needed to be assigned along with number of officers required for each duty (could be one or could be 10. That depends)

[ΚΑΘΗΜΕΡΙΝΕΣ ΥΠΗΡΕΣΙΕΣ] is TABLEC (table where duties and ids will be imported )

ΤΥΧΑΙΑ ΑΝΑΚΑΤΑΝΟΜΗ is a query that put the Records from TableA in a random Sequence ( i want to use that so every duty is assigned randomly to the officers, will put some more criteria later) (I think that's where i take the data from in the following appending query and not from TableA [ΠΡΟΣΩΠΙΚΟ])

[ΥΠΗΡΕΣΙΑ ΓΙΑ ΕΠΟΜΕΝΗ ΜΕΡΑ /ΑΝΑΓΚΕΣ ΚΑΙ ΑΤΟΜΑ] is the appending query that unfortunately multiply the data from |TableB| with the data from |TableA|
What i want though is that the field ID from [ΠΡΟΣΩΠΙΚΟ] or the query
[ΑΝΑΚΑΤΑΝΟΜΗ] and duties from [ΚΑΘΗΜΕΡΙΝΗ ΥΠΗΡΕΣΙΑ] are matched with each other.

This is where my problem is.
Instead of allocating one duty to a random officer , every officer is allocated to all duties.
So we want TableC [ΚΑΘΗΜΕΡΙΝΕΣ ΥΠΗΡΕΣΙΕΣ] to be populated by just four duties along with just ONE ID of the officer being allocated, but instead it gets 16 duties because every duty is assigned to all officers 4 [duties needed] * 4 [ids] = 16 [records] While it had to be 4 [duties] * 1[ID for every duty] = 4 [records] On tableC


[ΑΝΑΓΚΕΣ ΥΠΗΡΕΣΙΑΣ ΓΙΑ ΕΠΟΜΕΝΗ ΜΕΡΑ ΜΕ ΠΑΡΑΛΛΗΛΗ ΠΡΟΣΘΗΚΗ] is an other query there that just takes the data from TABLEB (the duties and the number of person that are needed for each duty) and appends them on |TableC|. This works just fine.

N Table is just a tally table i use to join with TableB so i can populate [TableC] with the needed numbers.

I am really sorry that all the tables and fields are in Greek but i designed it so almost everyone from my work can use it!! Thank you for helping me out!
IF you have any troubles with the language on that please let me know!
Attached Files
File Type: zip DB.zip (57.2 KB, 11 views)
Chrismeli is offline   Reply With Quote
Old 06-23-2019, 07:34 AM   #5
Chrismeli
Newly Registered User
 
Join Date: Jun 2019
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Chrismeli is on a distinguished road
Re: Appending Query Multiplies Data

Quote:
Originally Posted by jdraw View Post
Forgetting tables and database terms for the moment, what is it that needs to be accomplished -simple description, plain English?

How many "duties" are there really? Seems Patrol, Courts, Guards only.
It also seems you are scheduling for current day only. Is that correct?

If we had a 30,000 ft overview of the requirement, it might be helpful.
Good luck.
There are 182 duties ( Not every duty is needed each day) My department is responsible for all the courts. That's why there are so many duties.
For example in one court depending on the case and the prisoners needed to be transferred i may allocate one or two or even ten officers. That will vary.
On the table of the duties i have a checkmark and also a input for the number officers i need to allocate to a specific task. This will be imported to TableC
What i want is my query to allocate the duties i want with the officers from TableA ( I have put just 4 now in the table for the sake of argument, i will have to put all 250 or more) Into TABLEC which will contain
Duty DateStart TimeStart DateFinish TimeFinish ID .This table will be connected to Table A so i can view on which duties has each officer been allocated and how many hours he worked

By the way the input that will be popping up if you run the appending query [ΥΠΗΡΕΣΙΑ ΓΙΑ ΕΠΟΜΕΝΗ ΜΕΡΑ /ΑΝΑΓΚΕΣ ΚΑΙ ΑΤΟΜΑ] is a date input with the form of 23/06/2019 dd/mm/yyyy and will append the date into TableC
Chrismeli is offline   Reply With Quote
Old 06-23-2019, 07:42 AM   #6
Chrismeli
Newly Registered User
 
Join Date: Jun 2019
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Chrismeli is on a distinguished road
Re: Appending Query Multiplies Data

Wrong ZIp
Here is the right one Sorry!
Attached Files
File Type: zip DBPOLIC.zip (143.3 KB, 11 views)
Chrismeli is offline   Reply With Quote
Old 06-24-2019, 11:29 AM   #7
Chrismeli
Newly Registered User
 
Join Date: Jun 2019
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Chrismeli is on a distinguished road
Re: Appending Query Multiplies Data

Here it is in english! Thanks in Advance
Attached Files
File Type: zip DBPOLIC.zip (333.5 KB, 16 views)

Chrismeli is offline   Reply With Quote
Old 06-24-2019, 12:07 PM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,035
Thanks: 36
Thanked 721 Times in 704 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Appending Query Multiplies Data

Quote:
Originally Posted by Chrismeli View Post
Here it is in english! Thanks in Advance
Hi. Looking at your database, how does TableA related to TableB? In other words, how can we tell which Officer to assign to which duty? For example, TableA has four Officers, and TableB indicates, you need two (2) of them to be assigned to one duty and four (4) of them to be assigned to the other duty. So, out of the four Officers, which one should be assigned to the first duty?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-24-2019, 12:15 PM   #9
Chrismeli
Newly Registered User
 
Join Date: Jun 2019
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Chrismeli is on a distinguished road
Re: Appending Query Multiplies Data

Quote:
Originally Posted by theDBguy View Post
Hi. Looking at your database, how does TableA related to TableB? In other words, how can we tell which Officer to assign to which duty? For example, TableA has four Officers, and TableB indicates, you need two (2) of them to be assigned to one duty and four (4) of them to be assigned to the other duty. So, out of the four Officers, which one should be assigned to the first duty?
That is to be done randomly ( and i will put some more criteria on the way later!) That's why i made the query random distribution so TableC can get the IDS from there in a random sequence along with the duties and allocate them randomly to each other!
For example if i am to populate TABLEC with 100 records for the following date
(from tableB , i maybe need 10 people in one spot, 2 in an other and so on) i need the query to randomly retrieve 100 IDs from TableA or from the query Random Distribution ( Because in the query the sequence of id is random) and allocate every officer to a duty! Now if i am asking 200 duties and i have only 150 officers , the duties will remain vacant and i will inform the headquarters that we lack personnel!

EDIT : Table A and Table B are not related
Table A are the people
and Table B are the duties
Should they be related? Should i let the table know that some officers for example will work only as guards and others in the office or sth like that?
Chrismeli is offline   Reply With Quote
Old 06-24-2019, 03:23 PM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,941
Thanks: 79
Thanked 1,566 Times in 1,454 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Appending Query Multiplies Data

When you talk about how something "multiplies" your data by giving you extra records, you are technically correct in this sense: Access is a combinations engine because of the nature of Set Theory on which it is based. When you try a query to combine unrelated things, you get ALL of the possible combinations that don't otherwise violate your criteria (WHERE clauses). Therefore, the effect you describe COULD be just an improperly constrained multi-table query that appears to multiply two tables in the sense of a matrix-multiplication of two vectors.

You showed us this query earlier:

Code:
INSERT INTO TABLEC ( DUTY, DATE, ID )
SELECT TABLEB.DUTY, [INPUT DATE], TABLEA.ID
FROM TABLEA, TABLEB INNER JOIN n ON n.n <= TABLEB.[Number of Police needed for each service];
Look for the red comma in your FROM clause. That can't be there because you have introduced an ambiguous relationship. The INNER JOIN clause seems a bit off somehow. Normally you would have joined TABLE A INNER JOIN TABLE B ON and named two fields, one from each table, to constrain the JOIN operation.

If these two items really ARE unrelated to each other, you might need to generate something we call a JUNCTION table (which you can look up using this forum's search feature). Junction tables are how you join two disparate and otherwise unrelated entities, by building a bridging table to reflect an assignment that is neither a person nor a place, but that can link to both a person AND a place. It is quite commonly used when dealing with normalization issues.

There are other ways this could work, and I am not 100% sure given the description you have given so far, but it "smells" like that might be part of your answer.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Chrismeli (06-24-2019)
Old 06-24-2019, 04:15 PM   #11
Chrismeli
Newly Registered User
 
Join Date: Jun 2019
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Chrismeli is on a distinguished road
Re: Appending Query Multiplies Data

Quote:
Originally Posted by The_Doc_Man View Post
When you talk about how something "multiplies" your data by giving you extra records, you are technically correct in this sense: Access is a combinations engine because of the nature of Set Theory on which it is based. When you try a query to combine unrelated things, you get ALL of the possible combinations that don't otherwise violate your criteria (WHERE clauses). Therefore, the effect you describe COULD be just an improperly constrained multi-table query that appears to multiply two tables in the sense of a matrix-multiplication of two vectors.

You showed us this query earlier:

Code:
INSERT INTO TABLEC ( DUTY, DATE, ID )
SELECT TABLEB.DUTY, [INPUT DATE], TABLEA.ID
FROM TABLEA, TABLEB INNER JOIN n ON n.n <= TABLEB.[Number of Police needed for each service];
Look for the red comma in your FROM clause. That can't be there because you have introduced an ambiguous relationship. The INNER JOIN clause seems a bit off somehow. Normally you would have joined TABLE A INNER JOIN TABLE B ON and named two fields, one from each table, to constrain the JOIN operation.

If these two items really ARE unrelated to each other, you might need to generate something we call a JUNCTION table (which you can look up using this forum's search feature). Junction tables are how you join two disparate and otherwise unrelated entities, by building a bridging table to reflect an assignment that is neither a person nor a place, but that can link to both a person AND a place. It is quite commonly used when dealing with normalization issues.

There are other ways this could work, and I am not 100% sure given the description you have given so far, but it "smells" like that might be part of your answer.
Thank you! I will go ahead and check it right away!
Chrismeli is offline   Reply With Quote
Old 06-24-2019, 06:19 PM   #12
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,035
Thanks: 36
Thanked 721 Times in 704 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Appending Query Multiplies Data

Quote:
Originally Posted by Chrismeli View Post
That is to be done randomly ( and i will put some more criteria on the way later!) That's why i made the query random distribution so TableC can get the IDS from there in a random sequence along with the duties and allocate them randomly to each other!
For example if i am to populate TABLEC with 100 records for the following date
(from tableB , i maybe need 10 people in one spot, 2 in an other and so on) i need the query to randomly retrieve 100 IDs from TableA or from the query Random Distribution ( Because in the query the sequence of id is random) and allocate every officer to a duty! Now if i am asking 200 duties and i have only 150 officers , the duties will remain vacant and i will inform the headquarters that we lack personnel!

EDIT : Table A and Table B are not related
Table A are the people
and Table B are the duties
Should they be related? Should i let the table know that some officers for example will work only as guards and others in the office or sth like that?
Hi. Creating a relationship between officers and duty qualifications may still be necessary, but I don't think it will solve the current issue. Since you already have a query that works to populate the duty roster with the exact number of duties you require, then perhaps the next step is, in a completely separate step, to produce the same number of officers in random order to fill in those duties. You can then use code to plug in each random officer to the duty roster.

In other words, your working query was able to produce 6 rows of duty requirements. Now, create another query to produce 6 random officers from your table. You can then take this list of 6 random officers and use code to insert each one into the table created by the first query.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Tags
access , appending , query , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
totalling line values in a query before appending the data. Maclain General 3 10-21-2014 06:27 AM
Select Query multiplies records?? AnimeOnMyMind Queries 4 02-05-2014 02:35 PM
Appending or Updating data query newbieaccess Queries 2 04-14-2010 08:45 AM
appending table with ComboBox Data and the other data located on the form lebouffonvert Forms 2 01-28-2007 12:17 PM
Appending data to a field with an update query chablups Forms 7 02-14-2002 08:34 AM




All times are GMT -8. The time now is 07:27 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World