0

So i kind of i figured out the answer to my previous post on how to update - populate the database : "How to update-populate empty fields in A table with values from a B with a random sequence"

Now the problem I face is that the appending query multiplies the data with each other 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?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Chris Meli
  • 9
  • 3
  • MS Access or MySQL? What determines the `DUTY` that an officer in `TableA` can perform? – Lee Mac Jun 23 '19 at 00:22
  • How should query know that 28066 should be assigned only Patrol duty, etc.? Curious, is rank Serg misspelled? – June7 Jun 23 '19 at 00:27
  • @LeeMac Well supposedly all officers can perform all duties. I will put some more criteria as i go forward such as how many hours has the officer 'A' worked so if he has worked too many hours during the week i can assign him to a post that is only for a few hours. An other criteria is that a sergeant wont be working for example as a guard. That is work for constables. Which is an other criteria i will have to think on how i am going to insert in that table. – Chris Meli Jun 23 '19 at 01:09
  • @June7 ORDER BY Rnd(-Timer()*[ID]); That's how. LoL Ehm noo it is Serg. for Sergeant – Chris Meli Jun 23 '19 at 01:10
  • ORDER BY does not restrict officer to particular duty so that does not indicate 28066 should be assigned only Patrol duty. Ah, I was thinking of the slang Sarge. – June7 Jun 23 '19 at 01:15
  • Guys please help me out ! Currently there are 8 police officers working daily on this thing! 6 try to figure out every day on how to organize every post (like 300 people) helping on demands such as extra rest days or things like that. And they do all that on PAPER. Like we are in 1960. The other 2 are managing the police network and uploading the timetable of every day (which wont be necessary if all is created through the DB. with the Db and some help of autoit it will be a matter of one - two hours and 2 people) – Chris Meli Jun 23 '19 at 01:16
  • @june7 Ah not yet. I am still struggling on how to import duties and id s simultaneously. After i do that i will think about all the criteria. Such as rank, where someone leaves, when they prefer to work or where, who is better at what – Chris Meli Jun 23 '19 at 01:18
  • The process you describe sounds quite involved and quite possibly a query alone won't accomplish. If you can describe the manual decision process step by step then an algorithm should be able to replicate. Develop a decision flow chart then write code to implement. Perhaps a points weighting structure could assist in the assignment process. – June7 Jun 23 '19 at 01:31
  • @June7 True! It is quite complex. But what i really need now is just to sort out what is going wrong with this line of sql and why it multiplies the data between them and doesnt just allocate one to an other – Chris Meli Jun 23 '19 at 01:34
  • It multiplies because there is no link that restricts TABLEC ID to specific duty so it associates with all of them. This is known as cross join or Cartesian relationship. https://stackoverflow.com/questions/3022713/difference-between-full-join-inner-join – June7 Jun 23 '19 at 02:03
  • @june7 So what needs to be done is to put a left join on TableC right? FROM ((TABLEB INNER JOIN n ON n.n <= TABLEB.[Number of Police needed for each service]) LEFT JOIN TABLEC ON TABLEC.ID = TABLEA.ID) something like that? – Chris Meli Jun 23 '19 at 02:12
  • So try it and find out. Doubt will make difference because there is still no link that indicates ID association with specific duty. – June7 Jun 23 '19 at 02:15
  • @june7 thanks for taking the time to respond. I am really a noob at this and i have been trying the past few days to accumulate as much knowledge as i can regarding to sql/access ... I just want it to be randomly allocated! Don't care just yet – Chris Meli Jun 23 '19 at 02:18
  • Cross-posted https://access-programmers.co.uk/forums/showthread.php?t=305624 and https://www.utteraccess.com/forum/index.php?showtopic=2054214 – June7 Jun 24 '19 at 20:06
  • @June7 yes i did post in every access related forum because I need desperately a solution and trust me , I ve looked everywhere and couldnt find one. So maybe one who knows what i am doing wrong sees it by accidents and helps me out :/ – Chris Meli Jun 24 '19 at 20:08
  • That's fine but readers like to know so they can review avoid covering same ground. Your question is really too broad for SO. – June7 Jun 24 '19 at 23:24
  • @June7 dont worry! Once I find the solución I ll post it everywhere for the world to see! – Chris Meli Jun 24 '19 at 23:26
  • That would be nice but it is still considered proper etiquette to note the other threads so readers inclined to help can see what has already been done. – June7 Jun 24 '19 at 23:29

0 Answers0