A
~ angel ~ via AccessMonster.com
I need to display the results from a field in a table based on the following
information:
3 files - 2 tables and 1 query
1. ModelHeader (table with at least InvNo and Model) – Every invoice has a
Model
2. qryModelDetails (query with InvNo and Option_Item) – Invoice will have at
least one option_item to many option_item.
3. ProgramCodes (table with Model, Code, Option) – Model may have a Code and
sometimes the code will be different if the SAME Model as an Option. The
Option could be one or more.
Model may have a Code
Model that may have a Code may NOT have an Option
Query result:
InvNo, Model, Code – the invoice should only show once for the Code if there
is any and not repeat the Model if there’s Code for the Model and Option when
there isn’t an Option for the same Model on the invoice.
All suggestions would be very much appreciated.
i've found some Concatenating functions but it's not quite working. I don't
need to have the all the data from the Code field into one field on the
report, HOWEVER, i do need to have all the Code data on one line on the
report.
I am using a 3 column report to create labels. Each column / label is all the
data per invoice.
So, I want to show the data of the Code field in one area of the column and
it needs to show all the Code for that InvNo, Model, and Option (only if
there is one)
Basically the end result here I’m seeking needs to be:
InvNo Model Code AS ProgramCodes
12345 DC127T 1251R 127T (this invoice for Model = DC127T did
not have an Option)
23456 DC127T 1271R-RB 1271T-RB (this invoice for the same item,
DC127T does have an Option = RB)
34567 HF2 H1000RHT H101R-RB H102R-RB H200T-RB
The column / label (report):
InvNo: 12345
Model: DC127T
ProgramCodes: 1251R 127T <--NOTE there are no options for this invoice and
Model
InvNo: 23456
Model: DC127T
ProgramCodes: 1271R-RB 1271T-RB <--NOTE this is because this invoice the
Model: DC127T had an option: RB)
InvNo: 34567
Model: HF2
ProgramCodes: H1000RHT H101R-RB H102R-RB H200T-RB <--NOTE there are
no options for this invoice and Model
I hope this made sense and someone can help me! I can't figure it out!
information:
3 files - 2 tables and 1 query
1. ModelHeader (table with at least InvNo and Model) – Every invoice has a
Model
2. qryModelDetails (query with InvNo and Option_Item) – Invoice will have at
least one option_item to many option_item.
3. ProgramCodes (table with Model, Code, Option) – Model may have a Code and
sometimes the code will be different if the SAME Model as an Option. The
Option could be one or more.
Model may have a Code
Model that may have a Code may NOT have an Option
Query result:
InvNo, Model, Code – the invoice should only show once for the Code if there
is any and not repeat the Model if there’s Code for the Model and Option when
there isn’t an Option for the same Model on the invoice.
All suggestions would be very much appreciated.
i've found some Concatenating functions but it's not quite working. I don't
need to have the all the data from the Code field into one field on the
report, HOWEVER, i do need to have all the Code data on one line on the
report.
I am using a 3 column report to create labels. Each column / label is all the
data per invoice.
So, I want to show the data of the Code field in one area of the column and
it needs to show all the Code for that InvNo, Model, and Option (only if
there is one)
Basically the end result here I’m seeking needs to be:
InvNo Model Code AS ProgramCodes
12345 DC127T 1251R 127T (this invoice for Model = DC127T did
not have an Option)
23456 DC127T 1271R-RB 1271T-RB (this invoice for the same item,
DC127T does have an Option = RB)
34567 HF2 H1000RHT H101R-RB H102R-RB H200T-RB
The column / label (report):
InvNo: 12345
Model: DC127T
ProgramCodes: 1251R 127T <--NOTE there are no options for this invoice and
Model
InvNo: 23456
Model: DC127T
ProgramCodes: 1271R-RB 1271T-RB <--NOTE this is because this invoice the
Model: DC127T had an option: RB)
InvNo: 34567
Model: HF2
ProgramCodes: H1000RHT H101R-RB H102R-RB H200T-RB <--NOTE there are
no options for this invoice and Model
I hope this made sense and someone can help me! I can't figure it out!