Duplicating Data

  • Thread starter Thread starter naja
  • Start date Start date
N

naja

I have to process a cell phone bill that has multiple user and i have to
chargeback their division #. One division can have many users, but I hate to
key all their pertaining data each month. I would like to find a way that I
can only enter in this form just the new invoice # and the new charges. How
do I go about and do that?
 
Sounds like you are using ONE table, just like a spreadsheet. DON'T. That
is not how a relational database works.
Use tables in a one-to-many relationship --
Division -
Users -
Invoices

You create a table listing the Divisions with all the necessary information
about them and add one more field - DivisionID - Autonumber - primary key.

You create a table listing the Users with all the necessary information
about them and add two more fields - UserID - Autonumber - primary key and -
DivisionID - Number - Long Integer - foreign key.

Create the one-to-many relationship from [Division].[DivisionID] to
[Users].[DivisionID] and select Referential Integerity and Cascade Update.

Do same for Users/Invoices.

Create a form/subform with [Division]/[Users] and set Master/Child links
using DivisionID. Use a combo box to select User in the subform.
 
Hi Karl,

Thank you for helping me out with this project. I followed your
instructions, but when I select the name from the combobox it does not
synchronized with the main form and is not putting the value in the user
name, what is doing is putting the ID number. What did I do wrong? Thanks

KARL DEWEY said:
Sounds like you are using ONE table, just like a spreadsheet. DON'T. That
is not how a relational database works.
Use tables in a one-to-many relationship --
Division -
Users -
Invoices

You create a table listing the Divisions with all the necessary information
about them and add one more field - DivisionID - Autonumber - primary key.

You create a table listing the Users with all the necessary information
about them and add two more fields - UserID - Autonumber - primary key and -
DivisionID - Number - Long Integer - foreign key.

Create the one-to-many relationship from [Division].[DivisionID] to
[Users].[DivisionID] and select Referential Integerity and Cascade Update.

Do same for Users/Invoices.

Create a form/subform with [Division]/[Users] and set Master/Child links
using DivisionID. Use a combo box to select User in the subform.



naja said:
I have to process a cell phone bill that has multiple user and i have to
chargeback their division #. One division can have many users, but I hate to
key all their pertaining data each month. I would like to find a way that I
can only enter in this form just the new invoice # and the new charges. How
do I go about and do that?
Click to expand...
Click to expand...
 
Is your combo Column Count at least 2 and Column Widths like this - 0"; 1.5"


naja said:
Hi Karl,

Thank you for helping me out with this project. I followed your
instructions, but when I select the name from the combobox it does not
synchronized with the main form and is not putting the value in the user
name, what is doing is putting the ID number. What did I do wrong? Thanks

KARL DEWEY said:
Sounds like you are using ONE table, just like a spreadsheet. DON'T. That
is not how a relational database works.
Use tables in a one-to-many relationship --
Division -
Users -
Invoices

You create a table listing the Divisions with all the necessary information
about them and add one more field - DivisionID - Autonumber - primary key.

You create a table listing the Users with all the necessary information
about them and add two more fields - UserID - Autonumber - primary key and -
DivisionID - Number - Long Integer - foreign key.

Create the one-to-many relationship from [Division].[DivisionID] to
[Users].[DivisionID] and select Referential Integerity and Cascade Update.

Do same for Users/Invoices.

Create a form/subform with [Division]/[Users] and set Master/Child links
using DivisionID. Use a combo box to select User in the subform.



naja said:
I have to process a cell phone bill that has multiple user and i have to
chargeback their division #. One division can have many users, but I hate to
key all their pertaining data each month. I would like to find a way that I
can only enter in this form just the new invoice # and the new charges. How
do I go about and do that?
Click to expand...
Click to expand...
Click to expand...
 
No is not, so I will change that and let you know. Thx

KARL DEWEY said:
Is your combo Column Count at least 2 and Column Widths like this - 0"; 1.5"


naja said:
Hi Karl,

Thank you for helping me out with this project. I followed your
instructions, but when I select the name from the combobox it does not
synchronized with the main form and is not putting the value in the user
name, what is doing is putting the ID number. What did I do wrong? Thanks

KARL DEWEY said:
Sounds like you are using ONE table, just like a spreadsheet. DON'T. That
is not how a relational database works.
Use tables in a one-to-many relationship --
Division -
Users -
Invoices

You create a table listing the Divisions with all the necessary information
about them and add one more field - DivisionID - Autonumber - primary key.

You create a table listing the Users with all the necessary information
about them and add two more fields - UserID - Autonumber - primary key and -
DivisionID - Number - Long Integer - foreign key.

Create the one-to-many relationship from [Division].[DivisionID] to
[Users].[DivisionID] and select Referential Integerity and Cascade Update.

Do same for Users/Invoices.

Create a form/subform with [Division]/[Users] and set Master/Child links
using DivisionID. Use a combo box to select User in the subform.



:

I have to process a cell phone bill that has multiple user and i have to
chargeback their division #. One division can have many users, but I hate to
key all their pertaining data each month. I would like to find a way that I
can only enter in this form just the new invoice # and the new charges. How
do I go about and do that?
Click to expand...
Click to expand...
Click to expand...
 
Name this totals query MultiRefNum --
SELECT JCHSTTRN.Referencenumber, Count(JCHSTTRN.Referencenumber) AS
CountRefNum
FROM JCHSTTRN
WHERE Count(JCHSTTRN.Referencenumber) >1
GROUP BY JCHSTTRN.Referencenumber;

Your query joined with the query above --
SELECT DISTINCTROW JCHSTTRN.Jcreference, JCHSTTRN.Jctransactiontype,
JCHSTTRN.Transactiondate, JCHSTTRN.Unittype, JCHSTTRN.Description,
JCHSTTRN.Referencenumber, JCHSTTRN.Documentnumber, JCHSTTRN.Transactionunits,
JCHSTTRN.Amt, MultiRefNum.Referencenumber
FROM JCHSTTRN LEFT JOIN MultiRefNum ON JCHSTTRN.Referencenumber =
MultiRefNum.Referencenumber
WHERE (((JCHSTTRN.Jcreference) Not Like "*??????????3*" And
(JCHSTTRN.Jcreference) Not Like "??????????4") AND
((JCHSTTRN.Transactiondate)>=#1/1/2008#) AND
((JCHSTTRN.Transactionunits)=IIf([MultiRefNum].[Referencenumber] Is Not
Null,"0","1") Or (JCHSTTRN.Transactionunits)="0"))
ORDER BY JCHSTTRN.Jcreference, JCHSTTRN.Jctransactiontype,
JCHSTTRN.Transactiondate;
 
Disregard -- wrong thread.

KARL DEWEY said:
Name this totals query MultiRefNum --
SELECT JCHSTTRN.Referencenumber, Count(JCHSTTRN.Referencenumber) AS
CountRefNum
FROM JCHSTTRN
WHERE Count(JCHSTTRN.Referencenumber) >1
GROUP BY JCHSTTRN.Referencenumber;

Your query joined with the query above --
SELECT DISTINCTROW JCHSTTRN.Jcreference, JCHSTTRN.Jctransactiontype,
JCHSTTRN.Transactiondate, JCHSTTRN.Unittype, JCHSTTRN.Description,
JCHSTTRN.Referencenumber, JCHSTTRN.Documentnumber, JCHSTTRN.Transactionunits,
JCHSTTRN.Amt, MultiRefNum.Referencenumber
FROM JCHSTTRN LEFT JOIN MultiRefNum ON JCHSTTRN.Referencenumber =
MultiRefNum.Referencenumber
WHERE (((JCHSTTRN.Jcreference) Not Like "*??????????3*" And
(JCHSTTRN.Jcreference) Not Like "??????????4") AND
((JCHSTTRN.Transactiondate)>=#1/1/2008#) AND
((JCHSTTRN.Transactionunits)=IIf([MultiRefNum].[Referencenumber] Is Not
Null,"0","1") Or (JCHSTTRN.Transactionunits)="0"))
ORDER BY JCHSTTRN.Jcreference, JCHSTTRN.Jctransactiontype,
JCHSTTRN.Transactiondate;


naja said:
I have to process a cell phone bill that has multiple user and i have to
chargeback their division #. One division can have many users, but I hate to
key all their pertaining data each month. I would like to find a way that I
can only enter in this form just the new invoice # and the new charges. How
do I go about and do that?
Click to expand...
Click to expand...
 
Back
Top