Duplicating Data

[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?

 

[KARL DEWEY]

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]

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

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?

 

[KARL DEWEY]

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

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

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?

 

[naja]

No is not, so I will change that and let you know. Thx

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

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

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?

 

[KARL DEWEY]

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;

 

[KARL DEWEY]

Disregard -- wrong thread.

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;

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?