Relationship help

[Rikki]

Hi guys,

Sorry I do not know the terminology of what I need.

I am trying to setup a database to record some info on postcodes. What I need to do is, each supplier can have 4 types of lead (back data, profile, survey, 2nd use). I have two tables for suppliers and postcodes already. I also have 2 join tables for many to many relationships. I need each field in the postcodes database to be able to store a count for each supplier's lead type.

Eg:

Supplier    - Lead Type - Postcode - Count

Supplier A - Back Data - AB            - 20

                                     - BB            - 155

                                     - CB            - 30

 

                     Profile     - AB             - 55

                                    - BB             - 27

My problem is with the way I have set the relationships up currently. When I add a count for a postcode it adds that count for all suppliers. I need the postcode count to be specific to each supplier and lead type.

Can anyone tell me what I need to do to get this working correctly? I have attached a screenshot of my relationships.

Thanks

Edited February 2, 2017 by Rikki
mistakes

[Wim Decorte]

This sounds more like a report than a database to me.  That would mean that you don't need new fields in the Postcode table.

What is not clear to me in your structure: in what table/TO do you store the actual lead?  I'm assuming that the 'lead' table stores what supplier, what type and what postcode it is?

[Rikki]

15 hours ago, Wim Decorte said:

This sounds more like a report than a database to me.  That would mean that you don't need new fields in the Postcode table.

What is not clear to me in your structure: in what table/TO do you store the actual lead?  I'm assuming that the 'lead' table stores what supplier, what type and what postcode it is?

I can understand how you see that. I will try to explain a little better.

The database I am working on uses a new external file for each table. The leads table is actually in a different file and isn't referenced from here (the suppliers database) the count for each postcode isn't based on leads currently in the database, it is actually the count that the supplier is able to provide but we haven't yet purchased. In my screenshot the __Default table is actually the supplier database table.

[Wim Decorte]

I don't quite follow I must admit.  If you need to report on the leads table but it is not referenced in the file you sent us the screenshot from then how are you planning on getting to that leads data?

The closest I see this coming together is in that bottom row of TOs.

But the "supplier lead type join" table would need an fk for the post code on each record and that then would become the source TO for your report.

The "lead type postcode join" would go away.

[Rikki]

14 minutes ago, Wim Decorte said:

I don't quite follow I must admit.  If you need to report on the leads table but it is not referenced in the file you sent us the screenshot from then how are you planning on getting to that leads data?

The closest I see this coming together is in that bottom row of TOs.

But the "supplier lead type join" table would need an fk for the post code on each record and that then would become the source TO for your report.

The "lead type postcode join" would go away.

I don't actually need to report on any data in the leads table for this purpose. The supplier will send me a list like this:

Postcode	Count
AB	528
AL	222
B	1062
BA	408
BB	492
BD	426
BH	600
BL	342
BN	828
BR	264
BS	882
BT	1548
CA	492
CB	414
CF	1062
CH	618
CM	528
CO	582
CR	288
CT	438
CV	744
CW	276
DA	348
DD	324
DE	810
DG	264
DH	330
DL	396
DN	882
DT	240
DY	600
E	210
EH	678
EN	192
EX	684
FK	174
FY	390
G	648
GL	546
GU	528
HA	342
HD	252
Hg	102
HP	384
HR	276
HS	96
HU	522
HX	204
IG	240
IM	90
IP	618
IV	216
KA	414
KT	348
KW	120
KY	432
L	420
LA	312
LD	78
LE	1146
LL	678
LN	360
LS	534
LU	306
M	510
ME	534
MK	420
ML	354
N	198
NE	1422
NG	1320
NN	744
NP	594
NR	894
NW	150
OL	294
OX	552
PA	342
PE	1140
PH	138
PL	660
PO	948
PR	660
RG	624
RH	432
RM	462
S	972
SA	852
SE	408
SG	330
SK	474
SL	240
SM	150
SN	450
SO	540
SP	192
SR	210
SS	498
ST	642
SW	222
SY	432
TA	444
TD	138
TF	204
TN	726
TQ	282
TR	474
TS	816
TW	312
UB	240
WA	666
WD	150
WF	432
WN	408
WR	342
WS	492
WV	498
YO	816

 

My goal is to import these counts from an excel spreadsheet into the database so I can keep track of these counts for each of the 4 lead types for each supplier.

Edited February 3, 2017 by Rikki
mistakes

[Wim Decorte]

Importing that is straightforward, just add the supplier id to that and you're done.  But I don't see where the lead type comes into play with that data then...

[Rikki]

5 minutes ago, Wim Decorte said:

Importing that is straightforward, just add the supplier id to that and you're done.  But I don't see where the lead type comes into play with that data then...

This list was just an example of one "type" there can be 4 "types" but they all have the same sort of data just the source is different so I need to be able to distinguish between the 4 types of lead count (back data, profile, survey & 2nd use) there are around 30 suppliers in the table and 150 postcodes. I need all 30 suppliers to be able to track 4 different lead type counts.

[Wim Decorte]

I still don't understand the issue.  If you import that data, and at the moment of import you add the supplier ID and the lead type ID for the imported set then in effect you have a good table of of lead #s per supplier per postcode.  Once you have that, is that enough or do you need to do something with the data.